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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
=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:
Upvote 0
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.
 
Upvote 0
<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.
 
Upvote 0
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.
 
Upvote 0
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}))
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

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
Back
Top