Spreadsheet Practice

How to count cells that match a pattern in Google Sheets

Google Sheets formula

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

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

Result on the example
5 in C2
Excel
See below

See it work

The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result5

  1. A2:A109 rows × 1 col: "INV-2041", "INV-2042", "INV-20A3", "inv-2044", …

    A range: 9 cells in one column.

  2. REGEXMATCH(A2:A10,"^INV-\d{4}$"){9 rows × 1 col} TRUE, TRUE, FALSE, FALSE, …

    REGEXMATCH: TRUE if the text contains a match for the pattern.

    • textA2:A109 rows × 1 col: "INV-2041", "INV-2042", "INV-20A3", "inv-2044", …
    • regular_expression"^INV-\d{4}$"
  3. -REGEXMATCH(A2:A10,"^INV-\d{4}$"){9 rows × 1 col} -1, -1, 0, 0, …

    Makes the value negative.

  4. --REGEXMATCH(A2:A10,"^INV-\d{4}$"){9 rows × 1 col} 1, 1, 0, 0, …

    Makes the value negative.

  5. SUMPRODUCT(--REGEXMATCH(A2:A10,"^INV-\d{4}$"))5

    SUMPRODUCT: Multiplies arrays row by row and adds the results.

    • array1--REGEXMATCH(A2:A10,"^INV-\d{4}$"){9 rows × 1 col} 1, 1, 0, 0, …

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

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=COUNTA(FILTER(A2:A10,REGEXMATCH(A2:A10,"^INV-\d{4}$")))5Filter the matches and count them; returns 1 instead of 0 when nothing matches, so the SUMPRODUCT version is safer.
=COUNTIF(A2:A10,"INV-????")7Different resultCOUNTIF 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.

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 pattern

In C2, count the references in A2:A10 that are exactly INV- followed by four digits.

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}$"))).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.