Prev: Using SUMIFs on a changing pivot table - help
Next: List values based on different values selected
From: James on 4 Jun 2010 05:58
Hello all

I have a pivot table with revenue as the data, dates as the row fields and
the column fields as countries. I am using a SUMIF formula to add together
the revenue for numerous dates depending on what dates the user specifies.

The problem however is that the column fields in the pivot change if a new
country comes along and then the formula reads the wrong column.

For example, if I want to add up the revenue for the USA which is in column
C my formula would say {=SUMIF(A6:A10,B1,C6:C10)}. However if a new country
comes up e.g. Thailand and pushes the USA into column D then the above
formula would add up the revenue for Thailand not the USA.

Is there a better formula to use to do this?




--
James.
From: Luke M on 4 Jun 2010 08:29
Assuming your PivotTable has subtotals, you might be able to use some
version of the GETPIVOTDATA function. (see XL help)

--
Best Regards,

Luke M
"James" <James(a)discussions.microsoft.com> wrote in message
news:B9151BF4-E678-4475-A6C9-2F78BD2C87ED(a)microsoft.com...
> Hello all
>
> I have a pivot table with revenue as the data, dates as the row fields and
> the column fields as countries. I am using a SUMIF formula to add together
> the revenue for numerous dates depending on what dates the user specifies.
>
> The problem however is that the column fields in the pivot change if a new
> country comes along and then the formula reads the wrong column.
>
> For example, if I want to add up the revenue for the USA which is in
> column
> C my formula would say {=SUMIF(A6:A10,B1,C6:C10)}. However if a new
> country
> comes up e.g. Thailand and pushes the USA into column D then the above
> formula would add up the revenue for Thailand not the USA.
>
> Is there a better formula to use to do this?
>
>
>
>
> --
> James.


From: Roger Govier on 4 Jun 2010 11:58
Hi James

As Luke has told you, GetPivotData is designed to deal with exactly this
type of situation.
However, why are you wanting to do these calculations outside of the PT?
Can you not just filter for the range of dates you want, and use Grouping to
Group together the countries you need in your Summary

Which version of XL are you using?

--

Regards
Roger Govier

"James" <James(a)discussions.microsoft.com> wrote in message
news:B9151BF4-E678-4475-A6C9-2F78BD2C87ED(a)microsoft.com...
> Hello all
>
> I have a pivot table with revenue as the data, dates as the row fields and
> the column fields as countries. I am using a SUMIF formula to add together
> the revenue for numerous dates depending on what dates the user specifies.
>
> The problem however is that the column fields in the pivot change if a new
> country comes along and then the formula reads the wrong column.
>
> For example, if I want to add up the revenue for the USA which is in
> column
> C my formula would say {=SUMIF(A6:A10,B1,C6:C10)}. However if a new
> country
> comes up e.g. Thailand and pushes the USA into column D then the above
> formula would add up the revenue for Thailand not the USA.
>
> Is there a better formula to use to do this?
>
>
>
>
> --
> James.
>
> __________ Information from ESET Smart Security, version of virus
> signature database 5172 (20100604) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
>

__________ Information from ESET Smart Security, version of virus signature database 5172 (20100604) __________

The message was checked by ESET Smart Security.

http://www.eset.com



 | 
Pages: 1
Prev: Using SUMIFs on a changing pivot table - help
Next: List values based on different values selected