Subtotals in Pivot Tables

[Bovine Jones]

I've got a spreadsheet with a number of columns and I could do with putting a
subtotal into a column in the middle of the data. Currently the columns are:

Charged - Location - Size - Item

Every line in my source data has a value in the Size column which is the
same for each Location so that if I delete a line I don't end up without a
Size for a Location.... but I want to be able to total the Size for each
Charged and currently all I can do is get it to count every Size for every
Item which gives me the wrong answer. So for instance if I've got data of:

Charged - Customer A
Location A Size 1.6 Item A
Location A Size 1.6 Item B
Location B Size 1.6 Item A
Location B Size 1.6 Item B

What I need to end up with is a Pivot that tells me that Customer A has a
Size of 3.2 (because Location A and Location B are both 1.6) but currently
all I can get is that there are 4 items and so the total is 6.4.

Does that make sense? Can anybody assist?

 

[Bovine Jones]

Anybody?! It'll save me hours of work doing something else....

 

[Roger Govier]

Hi

Add another column to your source data, titled Size2 with this formula
=IF(SUMPRODUCT(($B$2:B2=B2)*($D$22=D2))>1,"",C2)

Extend the data range of your PT to include this new column.
Use Sum of Size2 as your data item, in place of Size and you should get the
result you want.

 

[Bovine Jones]

Thanks Roger. I think that does exactly what I want it do. Not sure how it
works though!