Excel : Count Unique values in a column


Excel Course : The Ultimate Excel Programmer Course

This tutorial explains how to count unique values in a column.

Sample Data
Sample Data
Formula
=SUMPRODUCT(1/COUNTIF(B3:B15,B3:B15))
Logic
The text "Jhonson" appears 3 times so the unique value would be equal to  (1/3) + (1/3) + (1/3) = 1

How Formula works

COUNTIF counts the number of times each value appears.
COUNTIF Formula Evaluation
Then all the values are divided by 1 and SUMPRODUCT sums all the fraction values.

Count Unique Values (Ignoring Blank Cells)
Data with Blank cells
Formula (Ignoring Blank cells)
=SUMPRODUCT((B3:B15<>"")/COUNTIF(B3:B15,B3:B15&""))

Related Articles
1. 3 Ways to extract unique values
2. Count Unique values in multiple columns
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 "Excel : Count Unique values in a column"

Post a Comment

Next → ← Prev