Getpivotdata subtotal

**dw_22801** · 06-26-2019, 11:50 AM

Hey,

I am trying to use GETPIVOTDATA for a subtotal below a pivot table, but cant seem to get it correct. I don't want to use the standard subtotal feature of the pivot table, as it puts to the right of the table. I need it at the bottom, below the Grand Total.

You can see there are two column fields, Station and Hauler. I am trying to get the total, or subtotal, of the Station column, regardless of the Hauler. Can I not do that? Do I have to choose a Station and Hauler? It seems to work on fields where there is only one Hauler under a Station, but if there is more than one Hauler, I get a #REF error. I am entering the formula in the orange row. You can see Station " Location 1" works in cell B9, but "Location 2" in E9 gives the error. I realize I can just =C8+D8+E8 and that will getpivot for those, but the problem is as I refresh this, Haulers within Stations change, and I have to redo that formula to include or exclude those Haulers.

Help!

Thanks!

**rorya** · 06-26-2019, 12:01 PM

GETPIVOTDATA only works to return the value of a cell in the pivot table. You'd need to sum an array of cells using something like this:

=SUM(GETPIVOTDATA("Net",$A$2,"Station","Location 2","Hauler",{"Hauler 1","Hauler 2","Hauler 3"}))

**dw_22801** · 06-26-2019, 03:02 PM

Thanks.

So if another hauler gets added to one of the locations, would I then have to add that hauler in the formula?

**rorya** · 06-27-2019, 04:06 AM

Yes, in some form or other. You could have a separate list of haulers and apply that to the formula with an IFERROR to handle any that might be missing.