A large dataset contains both unique and duplicate values. Sometimes, it might interest you to know the number of unique and duplicate values in your data. If you are a regular reader of our blog, you already know how to select Excel's unique values.
Unique values are values that do not appear more than once in a dataset.
Now, take a look at the table below:
Counting the number of unique values is one of the tasks that every excel user must do once in a while. So you do you go about this?
Excel provides functions that you can use for this task. You can achieve this task by combining the SUM function with the IF and COUNTIF functions.
Example: Let's count the number of unique names within the range, A2:A10. We will use the formula below:
=SUM(IF(COUNTIF(A2:A10,A2:A10)=1,1,0))
1. Just enter the formula in the formula bar, and your result will show up.
2. The formula contains three separate functions – SUM, IF, and COUNTIF.
3. The result is {1;2;2;1;2;2;2;1;2}.
4. COUNTIF function" counts how many times a particular number appears with the range. Take a look at the answer and see it for yourself.
5. 'IF function" analysis the results returned by the "COUNTIF" function. It maintains the 1's for unique values and replaces other values with "0."
6. Hence, the result, IF(1;2;2;1;2;2;2;1;2) = 1,1,0.
7. The array becomes {1;0;0;1;0;0;0;1;0}.
8. All the "1s" are unique values while the "0s" are duplicate values.
How to count the number of unique numeric values
The function we used earlier counts, both texts and numbers that are unique. To count unique numerals only, you have to include the ISNUMBER function in the formula for finding unique values.
=SUM(IF(ISNUMBER(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
The lowdown in this function is that it also counts dates and times.
How to count the number of unique text values in Excel
You will count the unique texts only if you include the ISTEXT function in the array formula as stated below:
=SUM(IF(ISTEXT(A2:A10)*COUNTIF(A2:A10,A2:A10)=1,1,0))
This formula will display the number of texts that are unique. It excludes errors, blank cells, logical numbers, numbers, etc.
Always press Ctrl + Shift + Enter when entering your array formula. The result will display like this:
