• 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.

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 dptutorials15@gmail.com


Best Laptops to use for better speed:

1️⃣ https://amzn.to/3lf8zYU

2️⃣ https://amzn.to/3xejpAW

3️⃣ https://amzn.to/379OqeL


Best Equipment & Tools for YouTube Channel : ➡️ https://bit.ly/3inKa1P


Our Recommendations

***************************************************************

Oracle Primavera Tutorials : https://bit.ly/3fn9PFH

Microsoft Excel Tutorials : https://bit.ly/2V5de5l

Microsoft Project Tutorials : https://bit.ly/37guNl7


For Personalized detail learning, write to dptutorials15@gmail.com


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 ► https://www.dptutorials.com


⚡️LEARNING RESOURCES I Recommend: https://www.dptutorials.com/resources


⚡️Subscribe for more Awesome Tutorials: http://goo.gl/NyAtg2


⚡️Support the Channel via shopping: https://amzn.to/2ZRfTOZ http://ift.tt/2jH38PR


⚡️Tools for YouTube vlogging:

***********************************************

• Laptop: https://amzn.to/2CaLFxJ

• Canon 200D Camera: https://amzn.to/3d7jDR4

• Benro Tripod: https://amzn.to/3exQoax

• Microphone: https://amzn.to/3c5lEvS

• Collar Microphone: https://amzn.to/2X8DWrS

• Screen recorder: https://techsmith.pxf.io/2BMjA

• Boom Arm Stand: https://amzn.to/3extb87

• Zoom H1 Audio Recorder: https://amzn.to/2TNdHFj

• Harison Softbox Studio Lights: https://amzn.to/3caGbzg

• Chroma Key Green Screen: https://amzn.to/2M60mn4

• Background Support Stand: https://amzn.to/3dbACSv

• Acoustic Foam Background: https://amzn.to/3gzFtyC

• USB RGB LED Strip: https://amzn.to/36BpCLF

• Wireless Mouse: https://amzn.to/2TPIrW7

***********************************************


⚡️You Can Connect with Me at:

***********************************************

YouTube: http://www.youtube.com/c/dptutorials

Instagram: https://www.instagram.com/dptutorials/

G+: http://ift.tt/2kAOpa6

Twitter: https://twitter.com/dptutorials15

Facebook: http://ift.tt/2kfRnDi

BlogSpot: http://ift.tt/2kB14dh

Websites: http://www.dptutorials.com & http://www.askplanner.blogspot.com


#dptutorials #Exceltraining #uniquevaluesinexcel #ExcelTips #ExcelFreeTraining #ExcelFreeLearning

0 comments