How do I sum up multiple items for the same field using cell references in GETPIVOTDATA?

[vanbasten007]

Hi everyone,

I hope someone here can help me. I want to sum up data for two periods (4&5) from a pivot table using GETPIVOTDATA formula. I've only been successful when I manually type the period values 4 and 5 as is shown below:

=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{4,5},"Project",$A7,"Category","T&M"))),0)

However, I cannot get the formula to work when I substitute cell references for the period values 4 and 5. Therefore, the following formula does not work:

=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) where E5 has 4 in the cell and E6 has 5.

How can I fix this?

Thanks!

[Pete_UK]

Are the values in E5/E6 proper numbers, whereas they are text values in your data (or vice versa)?

Hope this helps.

Pete

[vanbasten007]

I tried both number format and general format. But the thing is if I just refer to one cell, the formula works. However, when I try to sum up the two periods, it does not.

[benishiryo]

welcome to the forum vanbasten. try SUMPRODUCT.
=IFERROR(SUMPRODUCT(GETPIVOTDATA("Amt",'Transaction Pivot'!$J$3,"Period",$E$5:$E$6,"Project",A7,"Category","T&M")),0)

you could also use SUM, but you would need to press CTRL + SHIFT + ENTER to confirm the formula as it is an array formula when you have more than 1 criteria. and by the way, you could upload a sample excel file next time. i had to create a simulation to test & i'm not sure if it's exactly the same.

[vanbasten007]

Thanks very much! The formula does work. I will definitely post an example next time.

[bsmuller]

Hello, I am trying to create the same formula discussed in this thread but taking it a step further. I want to reference a specific sub set of "periods" in a list entered on a different tab instead of $e$5:$e$6. I've given the sub-set list a range name called "fixedincome". I've tried replacing $E$5:$e$6 with the range name but cannot get it to work. Is there a way to reference a list on another tab or range name instead of actually listing specific cells on the same sheet with the "GETPIVOTDATA" formula?

Thanks,
Brian

[bsmuller]

Never mind, I figured it out. The "IFERROR" was causing it. The pivot table I was referencing did not have one of the items in my sub-set list so it was returning zero. The range name worked once I changed my range.