I’m struggling with regex patterns in Google Sheets and need help with COUNTIF function.
I have product codes in this format: ##-#### where the numbers represent different categories and models.
For example, codes like 40-05## represent one category, and the last two digits specify the exact model number.
I’m trying to use this formula:
=COUNTIF(F5:F10,"40-05[456][789]*")
My data range F5:F10 has product codes formatted as text (otherwise Google Sheets messes up the formatting).
The formula above always returns 0, but when I simplify it to:
=COUNTIF(F5:F10,"40-05*")
It works fine. It seems like basic wildcards work but more complex pattern matching doesn’t. What am I doing wrong with the regex syntax in Google Sheets COUNTIF?
yeah, COUNTIF is a pain for regex. use QUERY instead: =QUERY(F5:F10,"select * where Col1 matches '40-05[456][789]'") but honestly, the SUMPRODUCT solution is way better. regex in google sheets can be kinda tricky.
COUNTIF doesn’t support regex patterns - it only handles basic wildcards (* and ?). Those [456] and [789] patterns you’re using are regex character classes that won’t work with COUNTIF. I hit the same wall when matching complex patterns in my inventory sheets. Use REGEXMATCH with SUMPRODUCT instead: =SUMPRODUCT(–(REGEXMATCH(F5:F10,“^40-05[456][789]$”))). The ^ and $ anchors match the entire cell content, not just parts. This counts cells where the third digit is 4, 5, or 6 and the fourth digit is 7, 8, or 9. COUNTIF works fine for simple wildcards, but you need REGEXMATCH for character classes or anything more sophisticated in Google Sheets.
Yeah, this is a super common issue - COUNTIF doesn’t support regex at all, just basic wildcards. Those bracket patterns like [456] are regex character classes, but COUNTIF just ignores them or treats them as literal text. I’ve dealt with this tons of times working with product catalogs and model numbers. Use ARRAYFORMULA with REGEXMATCH instead: =ARRAYFORMULA(SUM(IF(REGEXMATCH(F5:F10,"40-05[456][789]"),1,0))). This actually understands proper regex syntax and lets you build complex patterns while still counting matches. COUNTIF only gets you asterisks and question marks - that’s it.