Counting Unique Values in a List

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.

Screen Shot 2018-03-27 at 3.19.18 PM


The general format of the formula is:



How it Works

The trick is in knowing how the formula actually works.

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



  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!



1. Count unique values in a range with COUNTIF

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s