How to count cells that match a regex in Excel and Google Sheets
Excel formula
=SUMPRODUCT(--REGEXTEST(A2:A11,D1))Excel 365 has REGEXTEST: =SUMPRODUCT(--REGEXTEST(A2:A11,D1)). In Google Sheets, use REGEXMATCH in the same place.
- Result on the example
- 5 in
D2 - Works in
- Microsoft 365 only
- Google Sheets
- Different formula, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
The pattern ^[A-Z]{3}-\d{4}$ means: start of text, three capital letters, a hyphen, four digits, end of text. REGEXTEST (Excel) and REGEXMATCH (Google Sheets) return TRUE or FALSE for each code, and SUMPRODUCT counts the TRUEs.
Five codes pass. "QRS-12345" fails because $ requires the text to end after four digits, and "abc-1234" fails because the match is case-sensitive.
=SUMPRODUCT(--REGEXTEST(A2:A11,D1))Result5
A2:A1110 rows × 1 col: "ABC-1234", "AB-1234", "XYZ-0042", "abc-1234", …A range: 10 cells in one column.
D1"^[A-Z]{3}-\d{4}$"The value in D1.
REGEXTEST(A2:A11,D1){10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …REGEXTEST: TRUE if the text contains a match for a regular expression.- text
A2:A1110 rows × 1 col: "ABC-1234", "AB-1234", "XYZ-0042", "abc-1234", … - pattern
D1"^[A-Z]{3}-\d{4}$"
- text
-REGEXTEST(A2:A11,D1){10 rows × 1 col} -1, 0, -1, 0, …Makes the value negative.
--REGEXTEST(A2:A11,D1){10 rows × 1 col} 1, 0, 1, 0, …Makes the value negative.
SUMPRODUCT(--REGEXTEST(A2:A11,D1))5SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--REGEXTEST(A2:A11,D1){10 rows × 1 col} 1, 0, 1, 0, …
- array1
In Google Sheets
Google Sheets needs a different formula: =SUMPRODUCT(--REGEXMATCH(A2:A11,D1)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =COUNTIF(A2:A11,"???-????") | 7Different result | Wildcards in any version: checks the shape (three characters, hyphen, four characters) but not whether they are letters or digits. Also counts "abc-1234" and "JKL-12A4". |
Common mistakes
- Without
^and$the pattern can match part of the text, so "QRS-12345" would pass. Anchor patterns used for validation. - Regex is case-sensitive by default. Start the pattern with
(?i)to ignore case. - REGEXMATCH in Google Sheets only accepts text; a number in the range returns an error. Wrap the range as
A2:A11&"".
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.
Count cells that match a regex
D2, count the codes in A2:A11 that match the regular expression in D1.Questions
What if my Excel doesn't have REGEXTEST?
REGEXTEST is in Microsoft 365. In older versions, approximate the pattern with COUNTIF wildcards (? for one character, * for any) or check parts with LEFT, MID and ISNUMBER.
How do I count cells that contain any digit?
Use the pattern \d: =SUMPRODUCT(--REGEXTEST(A2:A11,"\d")).
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.