» » » Count Unique values based on multiple columns

Count Unique values based on multiple columns

1 Comment ,
This tutorial explains how to count unique values based on multiple columns in Excel.

Sample Data
Sample Data
Formula
=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
Related Posts
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.

1 Response to "Count Unique values based on multiple columns"

Next → ← Prev
Subscribe to: Post Comments (Atom)
Love this Post? Spread the Word!
Share