# Why COUNTIF with wildcards doesn't count numbers in Excel

Wildcards only match text, so `"*2026*"` skips real numbers. Search the digits instead: `=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))`.

**Excel:** `=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Order no. |  |  | Digits | 2026 |
| 2026014 |  |  | Orders |  |
| 2025118 |  |  |  |  |
| 2026021 |  |  |  |  |
| 2024307 |  |  |  |  |
| 2026105 |  |  |  |  |
| 2025220 |  |  |  |  |
| 1202600 |  |  |  |  |
| 2026117 |  |  |  |  |
| 2023099 |  |  |  |  |
| 2025031 |  |  |  |  |

Formula in E2: `=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))`

Result: 5

## How it works

COUNTIF with `"*"&E1&"*"` returns 0 here, because the order numbers are real numbers and wildcards are only applied to text. SEARCH doesn't have that limit: it reads each number as text and returns the position of "2026", or an error if it isn't there. ISNUMBER turns that into TRUE or FALSE, and SUMPRODUCT counts the TRUEs.

Five order numbers contain 2026, including 1202600, where it sits in the middle.

## Other ways

- `=COUNTIF(A2:A11,"*"&E1&"*")`: The formula that seems to fail. It works only when the order numbers are stored as text. (Returns 0 because wildcards ignore numbers.)
- `=SUMPRODUCT(--(LEFT(A2:A11,LEN(E1))=E1&""))`: Counts numbers that start with the digits. `E1&""` turns E1 into text so it can match what LEFT returns. (Leaves out 1202600, which contains 2026 but doesn't start with it.)

## Common mistakes

- LEFT, RIGHT and MID always return text, so `LEFT(A2,4)=2026` is FALSE. Compare with `"2026"` or wrap LEFT in VALUE.
- Numbers shown with formatting (like 2,026,014 or leading zeros) are searched as their plain digits.

## Questions

**How do I count numbers that start with certain digits?**

Use LEFT and compare text with text: `=SUMPRODUCT(--(LEFT(A2:A11,4)="2026"))`.

**Can I convert the numbers to text instead?**

Yes. A helper column with `=A2&""` or `=TEXT(A2,"0")` gives text that COUNTIF wildcards can match.

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/countif-wildcard-with-numbers