If you want to count the unique values in a range, you can do the same with the help of SUMPRODUCT Function and COUNTIF Function. In excel, when we talk about unique values, it means that we are counting the total no. of values that are present in a particular range but they are counted by keeping a point of view that the repetitive values will not be counted.

It must be confusing right now🤔, but to understand the same, let’s take an example to elaborate the above matter:

count unique values

As you can see in the image above, we have taken a bunch of data entries which are categories in two columns, i.e. Product and Sales.

count unique values

To count the unique values in the data, we will begin with the SUMPRODUCT Function which is as follows:

=SUMPRODUCT(

The following formula requires array 1, array 2, array 3, and so on…

count unique values

Then we will write 1 as we are having a count of unique values, so 1 is the most preffered number. Then we will divide the same by the COUNTIF Function.

count unique values

=COUNTIF(

The following formula requires range as well as criteria.

count unique values

As we want to find the unique values that are present in the product column or in other words we want to count the number of products we have in hand, our range will be the product range i.e. B2:B15.

count unique values

After range, comes the aspect of criteria, as we are looking for unique value, so the whole range is our criteria. By that, we will select the whole product range for the purpose of criteria, i.e. B2:B15.

count unique values

By this, our formula becomes as follows:

=SUMPRODUCT(1/COUNTIF(B2:B15,B2:B15))

count unique values

Now all we have to do is press enter and as you can observe in the image above, our answer is 6. This means that there are 6 unique values in the product range.

count unique values

So, it’s easy, isn’t it😏?

Learn: Get Month from date Using MONTH Formula