I need help with counting unique entries in a Google Sheets column that contains mixed data types. My column has order IDs mixed with quantity values, and I want to count only the distinct order IDs while ignoring duplicates and other data.
Each order ID ends with the same pattern, so I thought I could use that as a filter. However, I’m struggling to combine the uniqueness check with the text pattern matching.
My goal is to get the total count of different orders in the column, skipping any repeated order IDs and the quantity numbers that appear below them.
I attempted this formula: =countif('data'!B1:B, unique("*pattern")) but it’s not working as expected. What’s the correct way to achieve this?
Your countif and unique combo won’t work - those functions aren’t meant to be combined like that. countif needs criteria, not an array from unique. Here’s what you need to do: filter your data by the text pattern first, get unique values, then count them. Use this formula: =COUNTA(UNIQUE(FILTER(B:B,REGEXMATCH(B:B,"pattern$")))). Replace “pattern$” with whatever you’re looking for - the dollar sign makes it match the end of text. REGEXMATCH handles pattern matching, FILTER grabs matching entries, UNIQUE removes duplicates, and COUNTA counts everything. I’ve used this same setup for data cleanup and it handles mixed data types way better than nesting functions wrong.
maybe try using =UNIQUE(FILTER(B:B, ISNUMBER(SEARCH("*pattern", B:B)))) to get distinct values first, then count them, idk! works for me usually. also, make sure your data is in the right format, good luck!
I’ve had luck with SUMPRODUCT and COUNTIFS for distinct counting in similar cases. Try =SUMPRODUCT((COUNTIFS(B:B,B:B&"*",B:B,"*pattern")>0)/(COUNTIFS(B:B,B:B)+0.0001)) - just swap “pattern” for your actual text. This counts each match once, even with duplicates. SUMPRODUCT handles mixed data types better than other functions, and you skip the headache of array formulas or complex nesting. Really handy for messy datasets where you can’t predict what random data might show up between your target values.