Grand total of Distinct Values

[Agnethe]

Hi,

Would you be able to help me find a way to add Grandtotal as a sum of distinct values?
I have created a pivot - (see image attached) I use distinct count to give me the number of boxes per location.
In the grandtotal I would like to see the sum of distinct values. So grandtotal of boxsize IM should be 6 instead of 5, and Grandtotal of location B2 should be 5 instead of 3.
Is this possible?
Thank you in advance.

Pivot.jpg

[Special-K]

Post a sample spreadsheet not an image, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[Agnethe]

Thank you for the advise. I have attached the xls file.

[kersplash]

The grand total is giving you the grand total of distinct values in each Location which is correct.

You want the sum of all the distinct value counts, if that is what you actually want?

[Agnethe]

Yes, the sum of all the distinct value counts.