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
4
Medium Priority
?
3,153 Views
Last Modified: 2012-06-27
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
Comment
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
  • Learn & ask questions
  • 2
4 Comments
 
LVL 85

Expert Comment

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

Accepted Solution

by:
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:
calculated item
and the resulting table looks like:
Pivot Table Result with Total of the MAX items...

See attached:

Enjoy!

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

Expert Comment

by:dlmille
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

by:carlspywell
ID: 35205371
Pointed me in the right direction
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question