Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Pivot Table Total as sum When field is Max

Posted on 2011-02-22
Medium Priority
3,153 Views
If i have a pivot table that has a column with max values can i change the total to be the sum of the values and not the max?

i.e. I have a column showing the max values:

1000
2000
500
250
1750

The total is currently showing 2000 (the max), I need it to show 5500 (the sum)

Any ideas?
0
Question by:carlspywell
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 85

Expert Comment

ID: 34950595
You can't do that unless you add the field again as a sum.
0

LVL 42

Accepted Solution

dlmille earned 1500 total points
ID: 34990865
You could get rid of grand totals at the bottom, then create a calculated item called "Total of All" equal to max(field1) + max(field2) + max(field3) + ...

Click on the row labels, then select PivotTable ribbon element, Formulas, Calculated Item.  Then Add the new Item, and use the field list in the second listbox.  See below:

and the resulting table looks like:

See attached:

Enjoy!

Dave
Pivot-Sum-of-Max-r1.xlsx
0

LVL 42

Expert Comment

ID: 35065768
@Carl - additional issues, did this solution not work for you, or are you ready to close this out?

dave
0

Author Closing Comment

ID: 35205371
Pointed me in the right direction
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month11 days, 7 hours left to enroll