Why COUNTIF with wildcards doesn't count numbers in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))Wildcards only match text, so "*2026*" skips real numbers. Search the digits instead: =SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11))).
- Result on the example
- 5 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
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.
=SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))Result5
E12026The value in E1.
A2:A1110 rows × 1 col: 2026014, 2025118, 2026021, 2024307, …A range: 10 cells in one column.
SEARCH(E1,A2:A11){10 rows × 1 col} 1, #VALUE!, 1, #VALUE!, …SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
E12026 - within_text
A2:A1110 rows × 1 col: 2026014, 2025118, 2026021, 2024307, …
- find_text
ISNUMBER(SEARCH(E1,A2:A11)){10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …ISNUMBER: TRUE if the value is a number.- value
SEARCH(E1,A2:A11){10 rows × 1 col} 1, #VALUE!, 1, #VALUE!, …
- value
-ISNUMBER(SEARCH(E1,A2:A11)){10 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--ISNUMBER(SEARCH(E1,A2:A11)){10 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11)))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--ISNUMBER(SEARCH(E1,A2:A11)){10 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(--ISNUMBER(SEARCH(E1,A2:A11))). We ran it in Google Sheets mode on the same data and it returns 5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(A2:A11,"*"&E1&"*") | 0Different result | 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&"")) | 4Different result | 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)=2026is 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.
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 COUNTIF with wildcards doesn't count numbers
E2, count the order numbers in A2:A11 that contain the digits in E1.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.
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.