While working on Excel, users might often need to count how many unique or duplicate values are there in the dataset. Counting these values is easy with small datasets. However, when you are working with a large dataset, manually counting values isn’t viable. Fortunately, there are a few ways you can use to count the unique values on your Excel sheet and this guide will help you with it.
To count unique values in Excel, you can use an array formula that combines the COUNTIF and SUM functions. The COUNTIF function counts how many times a value appears in a range, and the SUM function adds up those counts. By dividing 1 by the COUNTIF result, we can get the fraction of each value that is unique. For example, if a value appears twice in a range, its fraction will be 1/2, and if it appears three times, its fraction will be 1/3. Then, by summing up these fractions, we can get the total number of unique values. Hence, to count the unique value, type the following formula:
=SUM(1/COUNTIF(range,range))
Suppose you want to count how many unique candies you ate in two months, you can use the following formula:
=SUM(1/COUNTIF(B2:C5,B2:C5))
As mentioned in the above figure, this formula will return the value 5, which is the number of unique candies you ate in the range B2:C5.
Hope this guide helps you to count unique values in Excel. Also, check out our other guides, such as How to delete duplicates in Excel or How to unhide rows in Excel.
In this guide, we'll explore the cost of skins in CS2, what determines their prices,…
The history of the UK is interconnected with the tale of bookmaking, reflecting the rich…
What are Arrowhead Game Studios plan to release Helldivers 2 on Xbox? Here are some…
Here is the formula on how to find Median of a dataset in Microsoft Excel.
Here's the list of all the FF7 Rebirth trophies including the Platinum trophy.
Here's how to redeem all the demo rewards and bonuses in Final Fantasy 7 Rebirth.