# How to count cells that match a regex in Excel and Google Sheets

Excel 365 has REGEXTEST: `=SUMPRODUCT(--REGEXTEST(A2:A11,D1))`. In Google Sheets, use REGEXMATCH in the same place.

**Excel:** `=SUMPRODUCT(--REGEXTEST(A2:A11,D1))` (Microsoft 365 only)

**Google Sheets:** `=SUMPRODUCT(--REGEXMATCH(A2:A11,D1))`

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Code |  | Pattern | ^[A-Z]{3}-\d{4}$ |
| ABC-1234 |  | Valid codes |  |
| AB-1234 |  |  |  |
| XYZ-0042 |  |  |  |
| abc-1234 |  |  |  |
| QRS-12345 |  |  |  |
| LMN-7781 |  |  |  |
| DEF 5521 |  |  |  |
| GHI-0900 |  |  |  |
| JKL-12A4 |  |  |  |
| TUV-3303 |  |  |  |

Formula in D2: `=SUMPRODUCT(--REGEXTEST(A2:A11,D1))`

Result: 5

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

## Other ways

- `=COUNTIF(A2:A11,"???-????")`: 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&""`.

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

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/count-cells-that-match-a-regular-expression