Hello,
I usually use SUMIFS with Excel tables because of the dynamic headers feature. If I move or insert columns in the table, no problem. I've created a simple Pivot Table from an Excel table, attached. in cell L12, the SUMIFS formula references the Excel table; cell L13 references the Pivot Table. They both work, but if I should change the structure of the Pivot Table by adding or eliminating columns, the SUMIFS formula will still reference the same columns as before. In addition, a SUMIFS formula in a Pivot Table (or any non-table range) has to allow for the possibility of different size ranges if the Pivot Table expands, unlike with a table, which automatically adjusts. That might mean specifying the whole column just to avoid having to check the formula each time the data changes (side note: Do hundreds of thousands of blank rows slow the calculation down?).
Is there a better way?
Paul
Hi Paul, did you try ´Getpivotdata´ to get the right information from the pivot table?
Frans
For example:
=GETPIVOTDATA("Sum of Total APCs",Sheet1!$B$6,"Maps To","Other","APC Group",LEFT(K11,2)+0,"APC Group Name","Emergency Services","MonthYear",TEXT(L11,"myyyy"))
No, I didn't try GETPIVOTDATA because I think it is only a lookup for a single value, not a sum.
Thanks, Velouria. I didn't see your post before replying, above. I didn't know the GETPIVOTDATA formula behaves similarly to SUMIFS. This makes it very flexible.
Thanks again, Paul
