I tried using Data Subtotal function which it counts the number of occurrences and in the Pivot table the counting/summing tools built into the Pivot Table are not able to distinguish between counting all items or one of each sub-category; it counts all instances of an entry. Bryon Smedley of Tennessee submitted a routine to myOnlineTraininghub to help solve this problem.

In the example below, the Pivot Table would return 8 for BRN-Dante and 15 for BRN-Green Valley. We simply want to know how many permits were issued per BU-Operation.

In other words, 2 permits for BRN – Dante and 6 permits for BRN – Green Valley.

Create a new column in the data and add the following formula next to the first record (cell C2):

=IF(SUMPRODUCT(($A1:$A$24=A1)*($B1:$B$24=B1))>1,0,1)

This will flag the first permit in each permit number group with 1 and all repeated permits with 0 and eliminates you from adding 1 and 0.

Picture1

 

The column can then be added to a  Pivot Table with the SUM function as the operator, counting the number of unique permits per BU-Operation indicating 8 permits in all.

Picture2 

Advertisements