اكسل

If you want to find the count of unique values in a single cell without extracting a separate list, then you can use a combination of SUM and COUNIF.

In this method, you just have to refer to the list of the values and the formula will return the number of unique values.

This is an array formula, so you need to enter it as an array, and while entering it use Ctrl + Shift + Enter.

And the formula is:

=SUM(1/COUNTIF(A2:A17,A2:A17))

When you enter this formula as an array it will look something like this.

{=SUM(1/COUNTIF(A2:A17,A2:A17))}

How it works

To understand this formula you need to break it down into three parts and just remember that we have entered this formula as an array and there are total 16 values in this list, not unique but total.

Ok, so look.

In the first part, you have used COUNIF to count the number of each value from 16 and here COUNTIF returns values like below.

In the second part, you have divided all the values with 1 which returns value like this.

Important Point: Let’s say if a value is there in the list twice, then it will return 0.5 for both of the values so that in the end when you sum it, it becomes 1 and if a value is there for three times it will return 0.333 for each.

And, in the third part, you have simply used the SUM function to sum all those values and you have a count of unique values.

This formula is quite powerful and it can help you to get the count in a single cell.