I have a spreadsheet with the following values for
1 1 1 2 3 3 4 5
I want to count the number of unique values
>= 3. (The values aren’t guaranteed to be sequential or with uniform step sizes)
This would seem trivial using
UNIQUE(), but oddly doesn’t work.
If I set
=UNIQUE(A1:A8), the result is a new column with the values we’d expect
1 2 3 4 5
However, if I then set
=COUNTIF(UNIQUE(A1:A8), ">=3"), I get the standard syntax error:
There's a problem with this formula. Not trying to write a formula? [...] To get around this, type an apostrophe ( ' ) first [...]
Obviously, if I remove the call to
=COUNTIF(A1:A8, ">=3")), everything works. I just get the wrong result, since it obviously counts the two
Trying to see if the problem is with
UNIQUE(), I tried replacing
=AVERAGE(UNIQUE(A1:A8))), but that works.
Is this expected behavior?
The problem is UNIQUE returns an array and COUNTIFS does not work with arrays.
Filter first and use COUNT:
What is interesting is if you use helper cells and spill
UNIQUE then refer to that it works.
This works because the formula creates a
Spill Range that the
B1# refers to. It is like a named range.
This method works with any version of Excel:
An Array (CSE) Formula in cell
Finish formula with Ctrl+Shift+Enter.
How it works:
FREQUENCYreturns an array of values that corresponds to filtered set of data for the data array.
IFfunction filters data.
Then each of these values tested to be
greater than or equals to 3, and looks like this.
SUMadds these values & returns the total, is
This formula can be extended to handle multiple criteria.
Adjust cell references in the formula as needed.