Excel: Only count unique cells from Countif

Question

Is there a way to only count unique cells from the CountIF function?

I have a table that is like:

My countif function is =COUNTIF(A1:A6,">2") to count all the cells that are >2. This returns 4 but it counts the number 3 twice. Is there a way to make it only count the unique values and have it return 3?

Answer 1

You need to create an array formula
This link explains how to do so: http://www.excel-easy.com/examples/count-unique-values.html

Answer 2

You can do this using FREQUENCY function, like this:

=SUM(IF(FREQUENCY(IF(A1:A6>2,A1:A6),A1:A6),1))

Confirm with CTRL+SHIFT+ENTER

See screenshot:

You could also use a "non-array" version along similar lines to Brockr22's suggestion, i.e.

=SUMPRODUCT((A1:A6>2)/COUNTIF(A1:A6,A1:A6&""))