1

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?

Improve this question

2 Answers 2

Reset to default
3

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

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.

Improve this answer
1
  • 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
0

:Caveat:

This method works with any version of Excel:

enter image description here

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

    {=SUM(--(FREQUENCY(IF(G55:G62>=3,G55:G62),G55:G62)>0))}

  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.

    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, is 3.

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

Improve this answer

You must log in to answer this question.

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