I have a spreadsheet with the following values for A1:A8
:
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 B1
to =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 C1
to =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 UNIQUE()
(=COUNTIF(A1:A8, ">=3")
), everything works. I just get the wrong result, since it obviously counts the two 3
‘s.
Trying to see if the problem is with UNIQUE()
, I tried replacing COUNTIF()
with AVERAGE()
(=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:
=COUNT(UNIQUE(FILTER(A1:A8,A1:A8>=3)))
What is interesting is if you use helper cells and spill UNIQUE
then refer to that it works. B1
= =UNIQUE(A1:A8)
and C1
= =COUNTIF(B1#,">=3")
This works because the formula creates a Spill Range
that the B1#
refers to. It is like a named range.
:Caveat:
This method works with any version of Excel:

An Array (CSE) Formula in cell
I55
:{=SUM((FREQUENCY(IF(G55:G62>=3,G55:G62),G55:G62)>0))}

Finish formula with Ctrl+Shift+Enter.
How it works:

FREQUENCY
returns an array of values that corresponds to filtered set of data for the data array. 
The
IF
function filters data.IF(G55:G62>=3,G55:G62

Then each of these values tested to be
greater than or equals to 3
, and looks like this.
{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}

SUM
adds these values & returns the total, is3
. 
This formula can be extended to handle multiple criteria.
{=SUM((FREQUENCY(IF((criteria1)*(criteria2),values),values)>0))}
N.B.
Adjust cell references in the formula as needed.