Formula Forensics 025. Count Unique Values in a Range

Formula Forensics 025. Count Unique Values in a Range:
This week at the Chandoo.org Forums, Ajinka asked a question about counting unique values in a range.
Faseeh answered with a neat Sumproduct() based formula and quoting a post that Chandoo had written at Chandoo.org answering the question in 2009.
A few people asked how it worked and Luke M gave a good response which I will be plagiarising in part here.
Faseeh’s formula was =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8))
As always at Formula Forensics you can follow along using a Worked Example which you can download here: Excel 97-2013.

Count Unique Values

Faseeh’s formula was =SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8))
So lets look at how that works
=SUMPRODUCT(1/COUNTIF(B2:B8,B2:B8))
The formula is a Sumproduct() based formula which tells us that the Sumproduct() function is being used to multiply and addup the component arrays. As there is only 1 array component in our formula, Sumproduct simply adds up the values. You can learn more about the Excel Sumproduct function here: Formula Forensics 007
The components of the Sumproduct() function are:
1/COUNTIF(B2:B8,B2:B8)
Lets start with the COUNTIF(B2:B8,B2:B8) part
In a blank cell F12 put =COUNTIF(B2:B8,B2:B8), press F9 instead of Enter

Excel will respond with ={3;1;2;3;3;2;1}

What is Countif() doing ?

The Syntax of Countif() is:


In our example COUNTIF(B2:B8,B2:B8) the Range and the Criteria are the same Range B2:B8

So Countif will Look at the Range (B2:B8) and see what matches the criteria in each cell in the Criteria Range (B2:B8), 1 cell at a time.
Lets look at the first few cells in the Criteria and work through them.
The first cell in the Criteria is B2 which contains “ABC”

We can see that the Range contains the first value in the criteria “ABC”, 3 times

This is the first 3 in the Array shown above ={3;1;2;3;3;2;1}



The second cell in the Criteria is B3 which contains “XYZ”

We can see that the Range contains the second value in the criteria “XYZ”, 1 times

This is the second element in the Array shown above ={3;1;2;3;3;2;1}



The third cell in the Criteria is B4 which contains “HML”

We can see that the Range contains the third value in the criteria “HML”, 2 times

This is the third element in the Array shown above ={3;1;2;3;3;2;1}



Stepping through the range and comparing each value in the criteria results in: ={3;1;2;3;3;2;1}

Reciprocal

The next part of the formula is the

1/COUNTIF(B2:B8,B2:B8)
This takes the reciprocal of our Array {3;1;2;3;3;2;1}
In a Blank cell F14 enter =1/COUNTIF(B2:B8,B2:B8) press F9 not Enter

Excel returns: ={0.333;1;0.5;0.333;0.333;0.5;1}  (I have truncated the 0.33333333333 values to save space)

Which is the same as {1/3; 1/1; 1/2; 1/3; 1/3; 1/2; 1/1}
The Sumproduct() function now steps in and adds up the values of the array returning the answer 4.

Summary

So generically if a value occurs T times in the range, it will occur T times in the criteria.
This will return the value T, T times. The smart bit here is taking the reciprocal of the Count.

So this means it will return the value T, 1/T times.
So ultimately T x (1/T) = 1.
You can see from the above it doesn’t matter how many times a value occurs, every unique value will be seen as 1 and then added up by Sumproduct

Download

You can download a copy of the above file and follow along, Download Here Excel 97-2013.

Formula Forensics “The Series”

This is the 25th or Silver Anniversary Post in the Formula Forensics series and was the first Formula Forensics completely developed in the new Office 2013.

You can learn more about how to pull Excel Formulas apart in the following posts

Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.

If you have a neat formula that you would like to share as Jong has done above, try putting pen to paper and draft up a Post like above or;

If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.