sum the highest 3 values in a pivot table row

[Gaille]

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

 

[nbrcrunch]

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]

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]

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]

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

 

[c_m]

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]

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

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]

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]

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.