• Amit Sarda

Counting Unique Values in a List

Updated: Dec 21, 2019

Problem and Dataset


I recently had a client who wanted a quick way to count the number of unique values in a list. In the following case, the number of unique names in the data in column A.


Counting Unique Values in a List


Formula


The general format of the formula is:

=SUMPRODUCT(1/COUNTIFS(data,data))

How it Works


The trick is in knowing how the formula actually works.


If you evaluate the formula, this is what you get:

=SUMPRODUCT(1/{7;7;7;7;7;1;7;7;1;2;2})

  1. The formula counts the number of instances of each name in the list (7; 7; 7; ….), and stores the count in an array.

  2. 1/Array creates fractions for each value in the list.

  3. SUMPRODUCT then sums up the fractions for each name (7*1/7, 2*1/2, etc.) and gives you the number of 1s in the calculation.

Genius, I say!

References

1. Count unique values in a range with COUNTIF

#data #formula #analytics #spreadsheets #Excel

11 views
 
  • LinkedIn
  • Twitter
  • Instagram

©2020 by Amit Sarda