Log In
Register
Home
July 7, 2016
Offline
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
VIP
April 21, 2015
Offline
Hi Paul, did you try ´Getpivotdata´ to get the right information from the pivot table?
Frans
Trusted Members
Moderators
November 1, 2018
Offline
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"))
1 Guest(s)
