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!

 

[Fazza]

what about if you do each period separately?. Untested

=IFERROR(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",E5,"Project",$A7,"Category","T&M"),0)+IFERROR(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",E6,"Project",$A7,"Category","T&M"),0)

 

[vanbasten007]

Yes, I am aware of this way. But I just want to understand why I can manually sum up periods by typing them in but cannot get the same result by referencing to cells that contain these values.

 

[Fazza]

Of course. Sorry. I don't know.

It looks like that is just how GETPIVOTDATA works. Refer Excel help. It is simply expecting an item name or a single cell, "the value of that cell is returned regardless of whether it is a string, number, error, and so on."

[Not what you're asking, but if you wanted the functionality write it in VBA in a user defined function.]

 

[KMolsberry]

vanbasten007: Instead of

=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) <array-entered>

rather than {E5,E6},
wouldn't you use just E5:E6 without the braces and with the colon? That way, you're passing a range address to the GETPIVOTDATA formula. Worked for me.

Beyond that, I then replaced the explicit range with a named range, so that my formula was like:

=SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",MyRange,"Project",$A7,"Category","T&M")) <array-entered>

That way, if the range changes, I don't have to worry about modifying the cell references in all those formulas - I just modify the address of the named range once.

And you might consider whether you have the SUM and IFERROR clauses in the correct order. I think what you want to do is say "If any of the terms returns an error, treat that term as 0". By having IFERROR outside the SUM, it seems that you're saying "If all of the terms summed together returns an error, treat the whole thing as 0". I think you want the IFERROR to be within the SUM so that IFERROR gets applied to each term of the SUM.