37

The documentation for pattern matching for Google Sheets has not been helpful. I've been reading and searching for a while now and can't find this particular issue. Maybe I'm having a hard time finding the correct terms to search for but here is the problem:

I have several numbers (part numbers) that follow this format: ##-####

Categories can be defined by the part numbers, i.e. 50-03## would be one product category, and the remaining 2 digits are specific for a model.

I've been trying to run this:

=countif(E9:E13,"50-03[123][012]*")

(E9:E13 contains the part number formatted as text. If I format it any other way, the values show up screwed up because Google Sheets thinks I'm writing a date or trying to do arithmetic.)

This returns 0 every time, unless I were to change to:

=countif(E9:E13,"50-03*")

So it seems like wildcards work, but pattern matching does not?

Michael
  • 8,362
  • 6
  • 61
  • 88
Martin
  • 447
  • 1
  • 5
  • 12
  • 3
    `COUNTIF` does not support regex, only wildcards: [*text strings can contain the `?` and `*` wildcard characters to match one (`?`) or multiple ( `*` ) contiguous characters*](http://spreadsheets.about.com/od/otherspreadsheets/qt/090804-google-spreadsheets-countif.htm). – Wiktor Stribiżew Nov 09 '16 at 20:31
  • Also see https://stackoverflow.com/a/18346191/241211 – Michael Mar 24 '19 at 23:05

2 Answers2

39

As you identified and Wiktor mentioned, COUNTIF only supports wildcards.

There are many ways to do what you want, though. To name two:

=ArrayFormula(SUM(--REGEXMATCH(E9:E13, "50-03[123][012]*")))

=COUNTA(FILTER(E9:E13, REGEXMATCH(E9:E13, "50-03[123][012]*")))
Michael
  • 8,362
  • 6
  • 61
  • 88
Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37
  • 3
    This does not count multiple occurrences within a cell. – Wes Modes Feb 05 '18 at 00:15
  • 3
    The `COUNTA()` method counts 1 even if there is no match – onlycparra Sep 30 '21 at 18:36
  • @onlycparra Turns out it's because FILTER returns N/A when there's no matches, and COUNTA counts N/A. If you wrap the FILTER in IFNA it works, e.g. COUNTA(IFNA(FILTER(...),)). The lack of an argument after the comma is intentional, it tells IFNA to return an empty cell. – Ben Sutton Aug 31 '22 at 05:42
6

This is a really big hammer for a problem like yours, but you can use QUERY to do something like this:

=QUERY(E9:E13, "select count(E) where E matches '50-03[123][012]' label count(E) ''")

The label bit is to prevent QUERY from adding an automatic header to the count() column.

The nice thing about this approach is that you can pull in other columns, too. Say that over in column H, you have a number of orders for each part. Then, you can take two cells and show both the count of parts and the sum of orders:

=QUERY(E9:H13, "select count(E), sum(H) where E matches '50-03[123][012]' label count(E) '', sum(H) ''")

I routinely find this question on $searchEngine and fail to notice that I linked another question with a similar problem and other relevant answers.

Michael
  • 8,362
  • 6
  • 61
  • 88