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:
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.
To count the unique values in the data, we will begin with the SUMPRODUCT Function which is as follows:
The following formula requires array 1, array 2, array 3, and so on…
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.
The following formula requires range as well as criteria.
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.
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.
By this, our formula becomes as follows:
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.
So, it’s easy, isn’t it😏?