# Can a subtotal Sum only unique values in a Pivot table?

1. ## Can a subtotal Sum only unique values in a Pivot table?

I created a Pivot table in Excel. The field setting for the data's values is set to Max.

I would like the pivot table subtotal to Sum those Max values, but when I change the subtotal field setting to Sum it adds all of the underlying data that's part of that set. Here's what the table looks like:

Capture.PNG

Here's a sort of text description, in case you can't see the picture.

Row Labels Max of Count
A 22
Name 1 5
Name 4 9

B 21
Name 1 7
Name 5 8

As you can see, the subtotals (in bold) are totaling all of the underlying data for that subtotal group. Is it possible to have the pivot table only subtotal the entries shown? So the subtotal for A would be 14 and the subtotal for B would be 15.

2. ## Re: Can a subtotal Sum only unique values in a Pivot table?

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

3. ## Re: Can a subtotal Sum only unique values in a Pivot table?

If you have Power Pivot, then yes you can write a measure for that. If not, you'd have to do something complicated with formulas in the source data, or outside the pivot table. Either way, a sample workbook would be useful as Glenn said.

4. ## Re: Can a subtotal Sum only unique values in a Pivot table?

Thank you for your responses. I should caveat this by saying it's not my data. I'm asking because someone had this question about their own data and I wasn't sure how to help.

I've attached a data set similar to what the person was asking. The data shows the increase in headcount expected in different factories across 5 regions.

We want the pivot to show the Max headcount increase in the region, and then (for some reason) we want the subtotal to sum only the rows listed in the pivot table. So for the East region, we want to sum the 5 and 9 max headcount increases at the Coffee Mugs and Post-It factories, but not the whole 22 headcount increase that we get if we sum all of the underlying data for factories in the East.

5. ## Re: Can a subtotal Sum only unique values in a Pivot table?

Do you have Power Pivot?

6. Originally Posted by rorya
Do you have Power Pivot?

7. ## Re: Can a subtotal Sum only unique values in a Pivot table?

Depending on what you want the Grand Total to show, you might use:

where Table1 is your source table. That would return 40 as the Grand Total since that is the sum of the maximum values for each of the Types. If you want the sum of the totals shown for each region, it would need a little more work. (I'm assuming the 10 in your sample pivot is not what you actually want).

If you want 64 as the Grand Total - i.e. the total of the totals by region, you could use:

There are currently 1 users browsing this thread. (0 members and 1 guests)