Possible solution for people looking to reference Grand Total in Pivot Chart

Osceana

Board Regular
Joined
Jan 24, 2010
Messages
125
I was trying to find a way to get a pivot table to include the grand total column for a call report I am doing. Seems it can't be done in a clean, easy way. Very frustrating! I would think adding the grand total column would be a no-brainer. Oh well. Through some experimentation I believe I found a workaround.

Basically what I did was make a static table. For my purposes, it has headers of 5:00 AM, 5:30 AM, 6:00 AM, etc. (This report shows how many calls were received at a certain time).

In the cell below each time header I put a Countif formula. It references another sheet in the workbook that has a full list of the calls. Here is the formula I use:

=COUNTIFS('Raw Call Data'!$C:$C,"="&1,'Raw Call Data'!$B:$B,"="&$B$42,'Raw Call Data'!$D:$D,">="&C$43)-COUNTIFS('Raw Call Data'!$C:$C,"="&1,'Raw Call Data'!$B:$B,"="&$B$42,'Raw Call Data'!$D:$D,">="&D$43)

It's basically saying "look for all the calls that have a code of 1 (a queue we have) and then subtract the number of calls greater than or equal to this time from all the calls greater than or equal to this time".

The 2nd criteria in that countif is what I just came up with though. It basically also tells the countif formula to only count calls that are equal to the date entered into a certain cell. That cell is a single cell of a pivot table I created. Basically I manually entered a bunch of dates (1/1/2012, 1/2/2012, etc.) in column A, then I inserted a pivot table a few cells below the date list in the same column based on those date entries. Then I inserted a slicer. From that slicer you can choose your date and the pivot table's cell will change dates based on what you pick. The cell in the pivot table is then, in turn, what's referenced in the static table's formulas, and then what the chart is based upon.

This is basically a crude way of making a "manual" pivot table, but it works surprisingly well. I believe it could be adapted to the "grand total" rather easily. Just make a "grand total" field in your static table and make the formula contingent upon the date you choose with your slicer as outlined above. Then you can just hide your static table and the pivot table depending on how you want to present it.

Anyway, I hope this helps someone, because it took me hours to figure this out and I saw no solutions anywhere online. It's QUITE frustrating. Hopefully Microsoft will include this functionality in their next version, as it's a rather glaring omission in my opinion.

***Also: if anyone has any suggestions on how to streamline this (particularly the formula), by all means, let me know. Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
*just to clarify on the pivot table part: I guess you can't put it directly below your manually-entered date range. It will tell you it can't change merged cells. Just put in the column or two next to the dates. The only field you will use is the dates in the row labels. Select only one of the dates. When you put the slicer in it will change that single cell automatically.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top