Count Unique values based on multiple columns


Excel Course : The Ultimate Excel Programmer Course

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
Coursera Data Science

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

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

Post a Comment

Next → ← Prev