## Explanation

*Note: Prior to Excel 365, Excel did not have a dedicated function to count unique values. This formula shows one way to count unique values, as long as they are numeric. If you have text values, or a mix of text and numbers, you'll need to use a more complicated formula.*

The Excel FREQUENCY function returns a frequency distribution, which is a summary table that shows the frequency of numeric values, organized in "bins". We use it here as a roundabout way to count unique numeric values.

Working from the inside-out, we supply the same set of numbers for both the data array and bins array to FREQUENCY:

```
FREQUENCY(B5:B14,B5:B14)
```

FREQUENCY returns an array with a count of each numeric value in the range:

```
{4;0;0;0;2;0;1;3;0;0;0}
```

The result is a bit cryptic, but the meaning is 905 appears four times, 773 appears two times, 801 appears once, and 963 appears three times.

FREQUENCY has a special feature that automatically returns zero for any numbers that have already appeared in the data array, which is why values are zero once a number has been encountered.

Next, each of these values is tested to be greater than zero:

```
{4;0;0;0;2;0;1;3;0;0;0}>0
```

The result is an array like this:

```
{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE}
```

Each TRUE represents a unique number in the list. The SUM ignores logical values by default, so we coerce TRUE and FALSE values to 1s and 0s with a double negative (--), which yields:

```
=SUM({1;0;0;0;1;0;1;1;0;0;0})
```

Finally, SUM adds these values up and returns the total, which in this case is 4.

*Note: you could also use SUMPRODUCT to sum the items in the array.*

### Using COUNTIF instead of FREQUENCY to count unique values

Another way to count unique numeric values is to use COUNTIF instead of FREQUENCY. This is a simpler formula, but beware that using COUNTIF on larger data sets to count unique values can cause performance issues. The FREQUENCY formula, while more complicated, calculates much faster.

### UNIQUE function in Excel 365

In Excel 365, the UNIQUE function provides a more elegant way to list unique values and count unique values.