This tutorial explains how to count unique values based on multiple columns in Excel.
Sample Data
 |
| Sample Data |
=SUMPRODUCT((1/COUNTIFS(B3:B15,B3:B15,C3:C15,C3:C15)))Logic
The combination 1 and Jhonson appears 2 times so the unique value would be equal to (1/2) + (1/2) = 1
How Formula Works
COUNTIFS counts the number of times the values appear based on multiple criteria.
 |
| COUNTIFS Formula Evaluation |
Then all the values are divided by 1 and SUMPRODUCT sums all the fraction values.
Count Unique Values (More than 2 columns)
 |
| Sample Data |
=SUMPRODUCT((1/COUNTIFS(B3:B15,B3:B15,C3:C15,C3:C15,D3:D15,D3:D15)))
Related Articles
1. 3 Ways to extract unique values
2. Count Unique values in a column
3. Select and Count Duplicate values in Excel
About Author:
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.
Post a comment