I’m trying to count specific items in my Google Sheets data using pattern matching but running into some issues. I have product codes that look like this format: ##-####
I need to group these codes by categories. For example, all codes starting with 40-02## belong to one category, and the last two numbers identify the specific item.
Here’s what I’m attempting:
=COUNTIF(A5:A10,"40-02[456][789]*")
My data range A5:A10 has the product codes formatted as text (otherwise Google Sheets messes them up thinking they’re dates).
This formula always gives me 0 results. However, if I simplify it to:
=COUNTIF(A5:A10,"40-02*")
Then it works fine. It looks like basic wildcards work but more complex patterns don’t. Am I doing something wrong with the bracket notation? Is there a different way to match specific digit ranges in COUNTIF? Any help would be great because I’ve been stuck on this for hours.
countif’s a bit limited, huh? yeah, try using sumproduct and regexmatch like this: =SUMPRODUCT(--(REGEXMATCH(A5:A10, "40-02[456][789]"))) this should match your pattern way better!
Google Sheets COUNTIF doesn’t support regex syntax like brackets - it only handles basic wildcards (* for multiple characters, ? for single). The SUMPRODUCT with REGEXMATCH solution mentioned above works great, but you could also use multiple COUNTIFS if you want. Something like =COUNTIFS(A5:A10,"40-024*")+COUNTIFS(A5:A10,"40-025*")+COUNTIFS(A5:A10,"40-026*") for each pattern you need. I’ve used this approach for simpler matching, but it gets messy with complex patterns. For what you’re doing, regex is definitely cleaner.
Had this exact headache last month with inventory codes. The bracket notation you’re using is regex syntax, but COUNTIF only gets basic wildcards. I switched to QUERY instead of SUMPRODUCT since it felt more natural coming from COUNTIF. Try =ROWS(QUERY(A5:A10,"select * where A matches '40-02[456][789].*'")) - gives you the count with proper regex matching. QUERY’s also easier to troubleshoot because you can see the actual matches first by dropping the ROWS wrapper. Just make sure your range doesn’t have empty cells or it’ll throw errors.