# sum the highest 3 values in a pivot table row

#### Gaille

##### New Member
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?

### 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.

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.

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.

=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:
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.

<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.

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.

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}))

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.

Replies
5
Views
588
Replies
1
Views
244
Replies
1
Views
454
Replies
4
Views
282
Replies
7
Views
159

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.

### Which adblocker are you using?

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

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