• Dp

How To Count Unique Values In A Range With COUNTIF In Excel

If you need to count the number of unique values in a range of cells in Excel, you can do that by using the combination of COUNTIF and SUMPRODUCT functions


Now in this tutorial, let us see how to count the unique values in a range with a function.


Link to download the exercise file: ➡️ https://bit.ly/3ji3kFg


In this example, I would be considering this data range from B4:C12.



and let us count the unique values in the cell F4.


Enter the formula in cell F4 as =SUMPRODUCT(1/COUNTIF(B5:C12,B5:C12)), Press enter to see the result as 8.


as we have the numbers 1 to 8 repeated twice, the unique count will be 8, which is right.

Let's test this formula, by typing the value as 10 in cell C5, then the unique count changes to 9 in the cell F4. you can as well test by typing 11 in cell C6, then our unique count will be 10.

So, in this way one can calculate the unique values in a range.


Now, let us see some use cases,


1: If I delete the cell C7, the formula is not working and showing error as shown below

That means, if our range contains any blank cell, this formula doesn't work.


So the solution is that we need to slightly adjust the formula as =SUMPRODUCT(1/COUNTIF(B5:C12,B5:C12&"")) and press enter to see the result as 11 now.