Why doesn't a SUMIF wildcard work with numbers in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)Wildcards like "12*" only match text, so test the leading digits as text instead: =SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8).
- Result on the example
- $10,505 in
E2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
SUMIF's * and ? wildcards work on text, and account 1201 is a number, so "12*" matches nothing and SUMIF returns 0. LEFT turns each account into text and takes its first two characters; comparing with E1&"" (12 as text) finds every account in the 12xx series.
=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)Result10505
A2:A87 rows × 1 col: 1201, 1250, 1340, 1205, …A range: 7 cells in one column.
E112The value in E1.
LEN(E1)2LEN: Number of characters.- text
E112
- text
LEFT(A2:A8,LEN(E1)){7 rows × 1 col} "12", "12", "13", "12", …LEFT: Characters from the start of text.- text
A2:A87 rows × 1 col: 1201, 1250, 1340, 1205, … - num_chars
LEN(E1)2
- text
E1&"""12"The & operator joins as text.
LEFT(A2:A8,LEN(E1))=E1&""{7 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
B2:B87 rows × 1 col: $4,500, $1,280, $2,750, $960, …A range: 7 cells in one column.
LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8{7 rows × 1 col} 4500, 1280, 0, 960, …The * operator multiplies.
SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)10505SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8{7 rows × 1 col} 4500, 1280, 0, 960, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8). We ran it in Google Sheets mode on the same data and it returns $10,505.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIFS(B2:B8,A2:A8,">="&E1*100,A2:A8,"<"&(E1+1)*100) | $10,505 | A numeric range, 1200 to 1299, when every code has four digits. Fast and needs no text conversion. |
| =SUMIF(A2:A8,E1&"*",B2:B8) | $0Different result | The wildcard version that fails. Matches no numeric codes: 0. |
Common mistakes
- Comparing LEFT's text result with the number 12 is always FALSE. Turn the input into text with
&"", as here. - Codes with leading zeros, like 0125, only keep the zero if they are stored as text. Numbers drop it.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Why doesn't a SUMIF wildcard work with numbers
E2, total the amounts for every account number that starts with the digits in E1.Questions
How do I sum codes that end with certain digits?
Use RIGHT: =SUMPRODUCT((RIGHT(A2:A8,2)="99")*B2:B8).
How do I sum codes that contain certain digits anywhere?
Use SEARCH, which works on numbers converted to text: =SUMPRODUCT(ISNUMBER(SEARCH(E1,A2:A8))*B2:B8).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.