# Apparent false syntax error using UNIQUE() in COUNTIF()

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.

• 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. May 27, 2020 at 22:22

:Caveat:

This method works with any version of Excel: 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.