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?

2 Answers 2


The problem is UNIQUE returns an array and COUNTIFS does not work with arrays.

Filter first and use COUNT:


enter image description here

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")

enter image description here

This works because the formula creates a Spill Range that the B1# refers to. It is like a named range.

  • Thanks. I'd realized that the "helper cells" method works, but it just wouldn't be feasible in my real use-case. But thanks, great explanation. Just waiting a short while for other answers before giving the checkmark.
    – Wasabi
    May 27, 2020 at 22:22


This method works with any version of Excel:

enter image description here

  1. An Array (CSE) Formula in cell I55:

  2. 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.


  • Then each of these values tested to be greater than or equals to 3, and looks like this.


  • SUM adds these values & returns the total, is 3.
  • This formula can be extended to handle multiple criteria.



Adjust cell references in the formula as needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .