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