Count Unique Values in a Column Using SUM and COUNTIF Functions
Using SUM and COUNTIF formulas together. This is the easiest and simplest way to find Distinct values in Excel.
Note: We can also use SUMPRODUCT instead of SUM.
Syntax:
COUNTIF (range, criteria)
- Range – List of data
- Criteria – A number of texts. Apply on the input range
Return an integer value that matches the criteria.
Step 1: Type “Number of Customers” in the cell.
Step 2: Write the below formula in any cell, to count the number of unique customers. We use the Array formula to compute unique count, So make sure you have to Press “Ctrl+Shift+Enter” for curly braces as shown in Image 1 below:
Formula:
” =SUM(1/COUNTIF(B2:B22,B2:B22)) “
Your formula in cell D7 should be covered with “{}“ Curly braces as shown below:
Note: The result of the COUNTIF formula is used as a divisor with 1 as the numerator. This modifies the result of the COUNTIF formula, the value that appears only once in the array becomes 1 and the numbers which have duplicate values will become fractions corresponding to the multiple.
How to Count Unique Values in Excel? 5 Easy Ways
Count Unique values in Excel is a feature that can help you to find the values that occur only once in the huge list of Excel. We often need to report the Unique number of customers purchased, the number of products in our stock, List of regions our business covered.
In this article, we explain how to count unique values in an Excel column.
Before moving ahead you should know the difference between unique values and distinct values.
- Unique Values: These are the values that occur only once. Unique Values from the list {1,2,3,1,4,2,7,5,7} are {3,4,5}.
- Distinct Values: These are the Values that are different. Distinct values from the list are {1,2,3,1,4,2,7,5,7} are {1,2,3,4,5,7}.
Contact Us