I’m having trouble with pattern matching in Google Sheets and need some help. I’ve been working with product codes that follow a specific format like ##-#### where the numbers mean different things.
For example, codes like 25-07## represent one product line, and the last two digits tell me which specific model it is.
I tried using this formula:
=COUNTIF(B5:B10,"25-07[456][789]*")
My data range B5:B10 has the product codes stored as text format. I have to keep them as text because Google Sheets gets confused and thinks they’re dates or math problems otherwise.
The weird thing is this formula always gives me 0 results. But if I simplify it to:
=COUNTIF(B5:B10,"25-07*")
Then it works fine and finds matches. It looks like the basic wildcard * works okay, but the more advanced pattern matching with brackets doesn’t work at all. Am I doing something wrong with the syntax, or does Google Sheets not support this kind of pattern matching in COUNTIF?
I encountered a similar issue previously while working with product data. Although you might expect COUNTIF to handle regex patterns, it actually falls short in this regard. Instead, I found that using a helper column with REGEXMATCH was effective for spotting matches. You could implement something like REGEXMATCH(B5,“25-07[456][789]”) in a new column, then apply COUNTIF to that for counting TTRUE results. This extra column gives you the pattern-match capability you seek. Alternatively, SUMPRODUCT combined with REGEXMATCH can give a comprehensive solution, though it may be less efficient with larger data sets.
yep, countif in google sheets is pretty limited with regex. u can only use basic wildcards, no brackets or complex stuff. maybe try the QUERY function? it lets u use regex which should solve your issue!
Google Sheets COUNTIF doesn’t support regular expressions with square brackets like you’re trying to use. The function only recognizes two wildcards: asterisk (*) for multiple characters and question mark (?) for single characters. When you use brackets, COUNTIF treats them as literal characters to match rather than regex patterns. For your specific need with product codes, you’ll want to switch to COUNTIFS with multiple criteria or use a combination of other functions. Another approach is using SUMPRODUCT with REGEX functions if you need that level of pattern matching control. The limitation is frustrating but it’s just how COUNTIF works in Sheets compared to more advanced spreadsheet applications.