Monday, March 21, 2016

How to count unique values in Pivot table

This is a very interesting problem where in many data analysts find pivot table worthless when it comes to counting distinct(or unique) occurrences of a value in a table.

The solution is as below.

Example:

Basically you want to count unique occurrences of each value from the column 'List'

1. Add an additional column next to it and input an array formula (for indexing) which would basically sort the values in a more meaningful order.


Formula at cell C3 is: 
=INDEX($B$3:$B$17,MATCH(SMALL(COUNTIF($B$3:$B$17,"<"&$B$3:$B$17),ROW(A1)),COUNTIF($B$3:$B$17,"<"&$B$3:$B$17),0))

Ctrl+Shift+Enter 
(to make it an array formula)

the above step is optional and can be skipped if concerned.

2. Now add another column 'Count' with the following formula. This countif formula would divide the total occurrences to fractions that would some up to '1'.


Formula at cell D3: 
=1/COUNTIF($C$3:$C$17,C3)

3. A pivot of the table (B2:D17) with the column 'Count' in the values field will show the unique count of each values in either 'List' or 'Array formula' column.


Well, I hope you have enjoyed this. Let me know if you want to know something more or have any specific question (questions.aweexcel@gmail.com)

Below is the link to the excel file I am talking about. 




No comments:

Post a Comment