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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|