Using SUMIFs on a changing Pivot Table - Help!

  • Thread starter Thread starter James
  • Start date Start date
J

James

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?
 
Assuming your PivotTable has subtotals, you might be able to use some
version of the GETPIVOTDATA function. (see XL help)
 
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 said:
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
Click to expand...

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

The message was checked by ESET Smart Security.

http://www.eset.com
 
Back
Top