Forum Discussion
Solved
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 sel...
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.
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.