Forum Discussion
Trying to use UNIQUE inside COUNTIF
Hi!
Im getting the kind of error you get when there's a sintax error in a formula when I try to use UNIQUE inside COUNTIF.
What I have is a column (S) that's concatenating month (column A) with seller's name (column M).
For example:
JanJon Doe
JanJon Doe
JanJane Doe
FebSomeone Else
I want to count unique values in column S, with an specific criteria (starts with Jan, for example).
Excel seems not to be liking what I'm trying to do.
If I do COUNTA(UNIQUE(...)) it works. Thing is that this gives me the grand total and I need to add the starts with... criteria so I can separate by month.
Also, if I do UNIQUE(...) on one cell (which will spill all the results) and then, on another cell COUNTIF using the cell from UNIQUE(...) and it's spilled values as a range, it also works. I don't understand why I can't skip this step and do it directly with UNIQUE inside COUNTIF. Isn't the return value fron unique a valid range?
I hope I was able to explain this clearly enough. Thanks in advance!
UNIQUE returns an array. The first argument of COUNTIF must be a range, not an array.
See Count unique values with criteria for a way to do this.
5 Replies
Just in case there is anything of interest, here is the file showing differing levels of complexity.
As HansVogelaar points out, once you have an array rather than a range reference, the limitation of the xxxIFS formulas renders them unusable. Usually the SUM and IF can be used as separate functions, though the calculation is less efficient. With 365, one can simply develop a formula step by step testing at each stage.
= LET( distinct, UNIQUE(data), matches?, LEFT(distinct,3)="Jan", filtered, FILTER(distinct,matches?), COUNTA(filtered) )Then again, why the concatenation?
= LET( filtered, FILTER(Array, month="Jan"), selected, UNIQUE(filtered), ROWS(selected) )A step further and you can have
= CountUniqueIfλ(Array, month, "Jan")Peter, thank you for your answers. I'm not quite there yet with excel's formula development, but I will try this in the future.
UNIQUE returns an array. The first argument of COUNTIF must be a range, not an array.
See Count unique values with criteria for a way to do this.
Hans, thank you so much for the prompt response. This definitely solves my issue.
