CODEWITHSUNDEEP
regexgoogle-sheetspattern-matching
Martin
Martin

Reputation: 577

Google Sheets Pattern Matching/RegEx for COUNTIF

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?

Upvotes: 40

Views: 50676

Answers (2)

Robin Gertenbach
Robin Gertenbach

Reputation: 10786

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]*")))

Upvotes: 43

Michael come lately
Michael come lately

Reputation: 9342

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.

Upvotes: 6

Related Questions