sum the highest 3 values in a pivot table row

Gaille

New Member
Joined
Apr 2, 2006
Messages
7
Hi, I have a Pivot Table with 10 columns of data (numbers with a value of 0 to 1000) and a Total per row - all okay (1500 or so records).
For each row, I need to add the highest 3 values from the 10 columns to make a new total. Is there an easy formula I can add to the Pivot to do this?
(from a novice asking)
Appreciate your assistance, Gaille
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
I wish I could claim the genius behind this. Unfortunately, neither can recall who IS the author, regardless...

You'll have to change the column & row references to start where you want. I chose A thru J because that is first 10 columns. And row 1 because it is the first row.

=max(a1:J1)
This will give you the highest

=AVERAGE(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)):INDEX(B:B,MATCH(9.99999999999999E+307,B:B)-3))
This will give you the 2nd highest

=AVERAGE(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)):INDEX(B:B,MATCH(9.99999999999999E+307,B:B)-2))
This will give you the 3rd highest.

Added together you'll get the sum of the top 3 highest.

I tested this with only 4 values.
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
I found another way. These are array formulas. must be entered using Ctrl-Shift-Enter.

=LARGE($A$1:$J$1,1)
=LARGE($A$1:$J$1,2)
=LARGE($A$1:$J$1,3)

The above will give the largest three numbers. You should be able to put them all in a single cell connected with plus signs (+) to give the sum of the highest 3 numbers.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
large() isn't an array formula - normal entry will do.

as you won't be able to get what you want from the pivot table, option is to do it in the data & add result to pivot - eg add flag if items are in top three & add the flag to the pivot & sum in the data area.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224

ADVERTISEMENT

=LARGE($A$1:$J$1,1)
=LARGE($A$1:$J$1,2)
=LARGE($A$1:$J$1,3)

If you're going to do that in some circumstance not involving pivot tables, just use one formula, example:

=SUM(LARGE(A1:J1500,{1,2,3}))

And as Paddy pointed out, still not an array, just Enter.
 
Last edited:

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
Here is an example:

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 139px;"> <col style="width: 95px;"> <col style="width: 71px;"> <col style="width: 71px;"> <col style="width: 71px;"> <col style="width: 71px;"> <col style="width: 71px;"> <col style="width: 71px;"> <col style="width: 74px;"> <col style="width: 85px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td> <td>D</td> <td>E</td> <td>F</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">1</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">2</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td> <td>Sum of # of items sold</td> <td>Salesperson</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td> <td>Item</td> <td>Bill</td> <td>Bob</td> <td>Chuck</td> <td>Susan</td> <td>Tom</td> <td>Vicki</td> <td>Will</td> <td>Grand Total</td> <td>Sum of top 2</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td> <td>A</td> <td style="text-align: right;">10</td> <td style="text-align: right;">10</td> <td>
</td> <td>
</td> <td style="text-align: right;">13</td> <td>
</td> <td>
</td> <td style="text-align: right;">33</td> <td style="text-align: right;">23</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td> <td>B</td> <td>
</td> <td style="text-align: right;">20</td> <td style="text-align: right;">4</td> <td>
</td> <td style="text-align: right;">20</td> <td>
</td> <td>
</td> <td style="text-align: right;">44</td> <td style="text-align: right;">40</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">7</td> <td>C</td> <td>
</td> <td>
</td> <td style="text-align: right;">12</td> <td style="text-align: right;">15</td> <td>
</td> <td>
</td> <td style="text-align: right;">13</td> <td style="text-align: right;">40</td> <td style="text-align: right;">28</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">8</td> <td>D</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">15</td> <td>
</td> <td style="text-align: right;">11</td> <td style="text-align: right;">9</td> <td style="text-align: right;">35</td> <td style="text-align: right;">26</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">9</td> <td>E</td> <td>
</td> <td>
</td> <td>
</td> <td style="text-align: right;">10</td> <td>
</td> <td style="text-align: right;">33</td> <td style="text-align: right;">19</td> <td style="text-align: right;">62</td> <td style="text-align: right;">52</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">10</td> <td>F</td> <td style="text-align: right;">17</td> <td style="text-align: right;">17</td> <td>
</td> <td>
</td> <td style="text-align: right;">9</td> <td>
</td> <td>
</td> <td style="text-align: right;">43</td> <td style="text-align: right;">34</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">11</td> <td>G</td> <td>
</td> <td style="text-align: right;">27</td> <td style="text-align: right;">30</td> <td>
</td> <td style="text-align: right;">9</td> <td>
</td> <td>
</td> <td style="text-align: right;">66</td> <td style="text-align: right;">57</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">12</td> <td>Grand Total</td> <td style="text-align: right;">27</td> <td style="text-align: right;">74</td> <td style="text-align: right;">46</td> <td style="text-align: right;">40</td> <td style="text-align: right;">51</td> <td style="text-align: right;">44</td> <td style="text-align: right;">41</td> <td style="text-align: right;">323</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">13</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td> <td>
</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-size: 10pt; color: rgb(0, 0, 0); font-family: Arial; background-color: rgb(255, 252, 249);"> <tbody> <tr> <td>Spreadsheet Formulas</td></tr> <tr> <td> <table style="font-size: 9pt; font-family: Arial;" border="1" cellpadding="2" cellspacing="0"> <tbody> <tr style="font-size: 10pt; background-color: rgb(202, 202, 202);"> <td>Cell</td> <td>Formula</td></tr> <tr> <td>J5</td> <td>{=SUM(LARGE(B5:H5,{1;2}))}</td></tr></tbody></table></td></tr> <tr> <td>Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
</td></tr></tbody></table>
Copy down the formula.

Columns A through I are part of pivot table. Column J calculates the sum of top 2 numbers.
I wouldnt recommend using this formula if you are going to filter salesperson (column area field) and you require grand total field.
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,224

ADVERTISEMENT

<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: rgb(0,255,0); BORDER-BOTTOM-COLOR: rgb(0,255,0); COLOR: rgb(0,0,0); BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: rgb(0,255,0); FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: rgb(255,252,249); BORDER-RIGHT-COLOR: rgb(0,255,0); BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: rgb(202,202,202)"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>J5</TD><TD>{=SUM(LARGE(B5:H5,{1;2}))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!
</TD></TR></TBODY></TABLE>
Did you read the above posts, this is not an array formula, just pressing Enter will do.
 

c_m

Well-known Member
Joined
May 29, 2008
Messages
836
Tom, you are right.
Thanks for pointing that out. (when you guys posted those posts, i was in the edit mode and didnt catch it)

Gaille, please make a note of this.
 

Gaille

New Member
Joined
Apr 2, 2006
Messages
7
Thank You to everyone for providing assistance, especially 'C_M' and 'Tom Urtis', this works perfectly and is so simple...... =SUM(LARGE(A3:H3,{1;2;3}))
 

nbrcrunch

Well-known Member
Joined
Jan 1, 2003
Messages
2,062
sorry for my stupid comment. I started to write an array formula and then changed my mind but forgot to remove the first sentence. I like Tom's solution considerably more anyway.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,141
Messages
5,599,966
Members
414,352
Latest member
macquarie_jchan58

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top