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?

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



"Bovine Jones" wrote:

> 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?

Hi

Add another column to your source data, titled Size2 with this formula
=IF(SUMPRODUCT(($B$2:B2=B2)*($D$2:D2=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.

--
Regards
Roger Govier

"Bovine Jones" <BovineJones@discussions.microsoft.com> wrote in message
news:18C4012A-F95F-40EA-A846-6A01BAB90E02@microsoft.com...
> 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?

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

"Roger Govier" wrote:

> Hi
>
> Add another column to your source data, titled Size2 with this formula
> =IF(SUMPRODUCT(($B$2:B2=B2)*($D$2:D2=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.
>
> --
> Regards
> Roger Govier
>
> "Bovine Jones" <BovineJones@discussions.microsoft.com> wrote in message
> news:18C4012A-F95F-40EA-A846-6A01BAB90E02@microsoft.com...
> > 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?
>