A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Using SUMIFs on a changing Pivot Table - Help!



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2010, 10:58 AM posted to microsoft.public.excel.misc
JAmes
external usenet poster
 
Posts: 904
Default Using SUMIFs on a changing Pivot Table - Help!

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.
  #2  
Old June 4th, 2010, 01:29 PM posted to microsoft.public.excel.misc
Luke M[_4_]
external usenet poster
 
Posts: 451
Default Using SUMIFs on a changing Pivot Table - Help!

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" wrote in message
...
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.



  #3  
Old June 4th, 2010, 04:58 PM posted to microsoft.public.excel.misc
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default Using SUMIFs on a changing Pivot Table - 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" wrote in message
...
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



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.