Counting unique values in Excel is a useful way to get a quick snapshot of the data you are working with. The COUNTIF function can be used to count unique values in Excel. In this blog post, we will learn how to count unique values in Excel using the COUNTIF function.
Guide How to Count Unique Values in Excel
First, select the cell where you want to display the count of unique values.
Type in the following formula.
=SUM(1/COUNTIF(A1:A8,A1:A8))This will create an array formula which will count the number of unique values in the selected range (A1:A8 in this case).
Press Enter to complete the formula.
This will apply the formula to the selected cell and display the count of unique values.
Things to Keep in Mind:
The COUNTIF function is case-sensitive, so if you have the same word with different capitalization, they will be counted as separate values.
The Excel formula may not work properly if the range being counted contains blank cells or errors. It’s important to make sure the range only contains the values you want to count.
This formula only counts unique values within the selected range. If there are duplicate values in other parts of the worksheet, they will not be included in the count.
Count Unique Values in Excel Example
Suppose you have a list of data in cells A1:A10, and you want to count the number of unique values in that list. You can use the following formula:
=SUM(1/COUNTIF(A1:A8,A1:A8))
This formula uses the “COUNTIF” function to count the number of times each value appears in the list, and then divides 1 by that count to get a fraction for each value.
The “SUM” function then adds up all the fractions, giving us the total count of unique values.
Here’s a breakdown of the formula:
- “COUNTIF(A1:A8,A1:A8)” returns an array of counts for each value in the range A1:A10.
- “1/COUNTIF(A1:A8,A1:A8)” returns an array of fractions for each value in the range A1:A10. For example, if the value “A” appears twice in the range, this will return the fraction 1/2 for that value.
- “SUM(1/COUNTIF(A1:A8,A1:A8))” adds up all the fractions to give us the total count of unique values in the range.
Note that this formula assumes that there are no blank cells or errors in the range. If your range contains blank cells or errors, you may need to modify the formula to account for them.
Additionally, if your range contains text values that are not case-sensitive (i.e., “Apple” and “apple” are considered the same value), you may want to convert the text to lowercase or uppercase before counting unique values.