Pivot Table Counting

[Missile Man]

I am trying to summarize my data in a Pivot Table. Here is an example of my
data.
There are 4 columns of data

Officer Name / Primary Num / Secondary Num / Car VIN

The Officer Name has one or more Primary Account Num.
The Primary Num is a 5 digit number.
The Primary Num has one ore more Secondary Num.
The Secondary Num is a 10 digit number & the first 5 digits are the Primary
Num.
The Secondary Num has only one VIN Num.

In a Pivot Table, I would like to summarize my data as follows:

Officer Num
Count of Primary Num by Officer Num
Count of Secondar Num by Primary Num

Is this possible?

Thanks,
Joe

 

[ShaneDevenshire]

Hi,

yes.
Place the Officer Name, Primary Num and Secondary Num fields in the Row
area. Place the Primary Num and Secondary Num fields in the Data area.
Change the calculation of the two data fields from SUM to COUNT. To make
things look better you might drag the Data button to the Column area.

 

[Debra Dalgleish]

There's no function in a pivot table that will count unique items. As a
workaround, you could add a field to the source data, to calculate the
count, and add that field to the pivot table. There's an example here:

http://www.contextures.com/xlPivot07.html#Unique

If the primary accounts are unique to one officer, you could use a
COUNTIF formula. If multiple officers might use the same primary
account, use SUMPRODUCT.