Sum of max in Excel Pivot Table

Question

Please do not mark this as duplicated of Sum of Max in PivotTable for Excel because there is no even answer for it.

I have a pivot table of items grouped by ID and month, and getting the maximum Q among all items. So the totals get the maximums as well.

    +------------+---------+----------------+
    |    A       |    B    |       C        |
  +-+------------+---------+----------------+
  |1|  Month     |  ID     |  Max(Q)        |
  +-+------------+---------+----------------+
  |2|  1         |  A      |  23            |
  +-+------------+---------+----------------+
  |3|  1         |  B      |  11            |
  +-+------------+---------+----------------+
  |4| Subtotal 1 |                      23  |
  +-+------------+---------+----------------+
  |5|  2         |  C      |  85            |
  +-+------------+---------+----------------+
  |6|  2         |  D      |  6             |
  +-+------------+---------+----------------+
  |7| Subtotal 2 |                      85  |
  +-+------------+--------------------------+
  |8|  Total                            85  |
  +-+---------------------------------------+

What I want to do is still get the max of each ID, but the totals to be the sum of the maximums. Like the following.

    +------------+---------+----------------+
    |    A       |    B    |       C        |
  +-+------------+---------+----------------+
  |1|  Month     |  ID     |  Sum(Max(Q))   |
  +-+------------+---------+----------------+
  |2|  1         |  A      |  23            |
  +-+------------+---------+----------------+
  |3|  1         |  B      |  11            |
  +-+------------+---------+----------------+
  |4| Subtotal 1 |                      34  |
  +-+------------+---------+----------------+
  |5|  2         |  C      |  85            |
  +-+------------+---------+----------------+
  |6|  2         |  D      |  6             |
  +-+------------+---------+----------------+
  |7| Subtotal 2 |                      91  |
  +-+------------+--------------------------+
  |8|  Total                           125  |
  +-+---------------------------------------+

I can't do the trick of this video https://www.youtube.com/watch?v=URfAkq0_dj0 because:

I can't alter the DB.
The origin table is in the data model so I think it's the reason why I'm not able to select a custom subtotal (Not sure, correct me if I'm wrong).

Any ideas?

Oh, also I'm open to use VBA.

Just a side note - you can't choose a question as duplicate unless it has an upvoted or accepted answer, so the one you linked isn't applicable. Can you include a screenshot of your actual pivot table and how it's setup? — dwirony, Apr 09 '19 at 21:56

score 0 · Answer 1 · answered Feb 01 '20 at 03:48

You can't do it in pivot. But if you can add helper column to source table, you can use following array formula to calculate MAX value in group and then use this column in pivot:

{=IF(SUM((A2=$A$2:$A2)*(B2=$B$2:$B2))=1;MAX((A2=$A$2:$A$15)*(B2=$B$2:$B$15)*($C$2:$C$15));0)}

Change semicolons to commas if needed.

Array formula after editing is confirmed by pressing ctrl + shift + enter

score -2 · Answer 2 · answered Apr 09 '19 at 22:06

-2

Assuming your pivottable looks something like this:

You can right click the subtotal line, go to "Summarize Values By" and select "Sum":

Which results in:

answered Apr 09 '19 at 22:06

dwirony

5,487
3
21
43

This would work if I had only one item per ID, where the sum and the max are the same value. The thing is I must find the max item per ID -setting the summary to Max- then sum the maxes on each month. – Javier Apr 10 '19 at 15:46
@Javier Have you tried messing around with the running totals? – dwirony Apr 10 '19 at 16:53
I almost got it showing values as running total in ID. The last ID actually showed the sum of all maxes, but next I need to link the result to another cell. Because of the changing input, the last ID is never the same to `GETPIVOTDATA` it, and it is never placed in the same cell to directly reference it. – Javier Apr 10 '19 at 18:50
Probably the solution is a formula in the destination cell. – Javier Apr 10 '19 at 18:53
1

Actually my solution was to keep the pivot table showing maxes, then use a `VLOOKUP` in destination cells to get the respective sums. – Javier Apr 30 '19 at 18:09

Sum of max in Excel Pivot Table

2 Answers2