# Why doesn't a SUMIF wildcard work with numbers in Excel

Wildcards like "12*" only match text, so test the leading digits as text instead: `=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)`.

**Excel:** `=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Account | Amount |  | Starts with | 12 |
| 1201 | 4500 |  | Total |  |
| 1250 | 1280 |  |  |  |
| 1340 | 2750 |  |  |  |
| 1205 | 960 |  |  |  |
| 1299 | 3125 |  |  |  |
| 2210 | 1875 |  |  |  |
| 1218 | 640 |  |  |  |

Formula in E2: `=SUMPRODUCT((LEFT(A2:A8,LEN(E1))=E1&"")*B2:B8)`

Result: $10,505

## 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.

## Other ways

- `=SUMIFS(B2:B8,A2:A8,">="&E1*100,A2:A8,"<"&(E1+1)*100)`: A numeric range, 1200 to 1299, when every code has four digits. Fast and needs no text conversion.
- `=SUMIF(A2:A8,E1&"*",B2:B8)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sumif-wildcard-with-numbers