# How to count cells that match a pattern in Google Sheets

Turn REGEXMATCH's TRUE and FALSE into 1 and 0 and add them: `=SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$"))`.

**Google Sheets:** `=SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$"))`

**Excel:** Microsoft 365 has REGEXTEST: `=SUM(--REGEXTEST(A2:A10,"^INV-\d{4}$"))`. Other versions can only approximate the pattern with COUNTIF wildcards, which can't require digits.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| Invoice ref |  | Valid refs |
| INV-2041 |  |  |
| INV-2042 |  |  |
| INV-20A3 |  |  |
| inv-2044 |  |  |
| INV-2045 |  |  |
| INV 2046 |  |  |
| INV-2047 |  |  |
| INV-20481 |  |  |
| INV-2049 |  |  |

Formula in C2: `=SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$"))`

Result: 5

## How it works

REGEXMATCH tests every reference at once inside SUMPRODUCT. The pattern is anchored at both ends, so INV-20481 (five digits), INV 2046 (a space), INV-20A3 (a letter) and inv-2044 (lowercase) all fail. The double minus turns TRUE into 1 and FALSE into 0, and SUMPRODUCT adds them: 5.

## Other ways

- `=COUNTA(FILTER(A2:A10,REGEXMATCH(A2:A10,"^INV-\d{4}$")))`: Filter the matches and count them; returns 1 instead of 0 when nothing matches, so the SUMPRODUCT version is safer.
- `=COUNTIF(A2:A10,"INV-????")`: COUNTIF wildcards can't require digits, and they ignore case. (Counts 7: it also accepts INV-20A3 and inv-2044.)

## Common mistakes

- Without `^` and `$`, INV-20481 passes because it contains INV-2048.
- REGEXMATCH needs text; if the range holds numbers, convert them with TO_TEXT inside ARRAYFORMULA.

## Questions

**How do I count matches ignoring case?**

Add (?i): `"(?i)^INV-\d{4}$"` also counts inv-2044.

**How do I list the invalid ones?**

Filter with NOT: `=FILTER(A2:A10,NOT(REGEXMATCH(A2:A10,"^INV-\d{4}$")))`.

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-pattern