top of page
  • Writer's pictureDp

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: ➡️

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.

2: Let us change the values of C5, C6 to 1,2 respectively and see that the unique values count is shown as 9,

that means this formula is treating the blank cell as a unique value. You can test by deleting one more value and observe that the unique count still remains as 9.

3: If you want to exclude the blank cell from the count, then you need to teak the formula further like this, type the formula in cell F4 as =SUMPRODUCT((B5:C12<>"")/COUNTIF(B5:C12,B5:C12&"")) and press enter to see the result as 8. So this formula is counting the unique values excluding the blank cells.

So friends, this is all about counting unique values in a range with the combination of Countif and Sumproduct functions

I hope you have enjoyed this tutorial, if yes please like share and comment.

For further more interesting videos, please do subscribe to dptutorials.

Watch this video tutorial for better understanding:

If you liked this tutorial, share it with your friends. And also you can follow us on Youtube, Twitter and Facebook. We would love to hear from you, Please do comment, suggest or compliment our work and we shall make it better for you. You can write us at

Best Laptops to use for better speed:




Best Equipment & Tools for YouTube Channel : ➡️

Our Recommendations


Oracle Primavera Tutorials :

Microsoft Excel Tutorials :

Microsoft Project Tutorials :

For Personalized detail learning, write to

If you found this video valuable, give it a like. If you know someone who needs to see it, share it. Leave a comment below with your thoughts. Add it to a playlist if you want to watch it later.


★ My Online Tutorials ►


⚡️Subscribe for more Awesome Tutorials:

⚡️Support the Channel via shopping:

⚡️Tools for YouTube vlogging:


• Laptop:

• Canon 200D Camera:

• Benro Tripod:

• Microphone:

• Collar Microphone:

• Screen recorder:

• Boom Arm Stand:

• Zoom H1 Audio Recorder:

• Harison Softbox Studio Lights:

• Chroma Key Green Screen:

• Background Support Stand:

• Acoustic Foam Background:

• USB RGB LED Strip:

• Wireless Mouse:


⚡️You Can Connect with Me at:








Websites: &


Recent Posts

See All


bottom of page