Facing "Number Outside Valid Range" error in Numbers

SUM(LARGE(B14:I14;{1;2;3;4;5})) might work in Excel (as an array formula) but not in Numbers. You only have five of them to add so you can add them up the old way:

=LARGE(B14:I14;1)+LARGE(B14:I14;2)+LARGE(B14:I14;3)+LARGE(B14:I14;4)+LARGE(B14:I14;5)

I am assuming "LARGEEST" was actually the "LARGE" function.

Use:

SUM(LARGE(B14:I14,5))

(254720)

Camelot wrote:

=SUMIF(B14:I14,">="&LARGE(B14:I14,5))

This may not give you the same answer as summing the 5 largest values. As an example, consider the set of numbers 10,9,8,7,6,6,6,5,4,3,2,1. Sum of the top 5 = 10+9+8+7+6. Sum of ">=5th largest" = 10+9+8+7+6+6+6.

> This may not give you the same answer as summing the 5 largest values

I did mention that in my answer, noting that the original post wasn't clear what to do in this case.

If you want the sum of all cells with the highest 5 values (rather than the highest 5 cells), then amending the formula to:

 =SUMIF(B14:I14,">"&LARGE(B14:I14,6))

will do that - essentially summing the values of everything greater than the 6th highest value.

Note that the same consideration is in play with your solution where you are specifically adding 5 cell values, not accounting for multiples. Without knowing the OPs expectation on what to do with multiple equivalent values it's impossible to answer for sure.

Here is a general way to sum a specified number of cells (5 in this case) without having to add them up separately:

=SUMIF(B,">="&LARGE(B,5))−LARGE(B,5)×(COUNTIF(B,">="&LARGE(B,5))−5)

The duplicates will all be equal to the 5th largest. Count how many cells actually got added and subtract the value of the extra ones.

The other answers get close, but the simplest method is using =SUMIF()

=SUMIF(B14:I14,">="&LARGE(B14:I14,5))

The LARGE() function searches the 5th highest value in the range B14:I14

The SUMIF() function then sums all the values in the range that are greater than or equal to the result of the LARGE() function.

Care may be needed if there are multiple values - if there are multiple cells with the same 5th-highest value, then they will all be summed, but it isn't clear what your use case or design expectations are in this case.

I based my answer on the formula provided by the OP which was designed to sum five values.

=SUMIF(B14:I14,">"&LARGE(B14:I14,6)) may not sum the 5 highest values. In my example set, the 5th largest is the same as the 6th largest so the formula sums only the top 4. Your original formula correctly sums the top 5 plus any duplicates equal to the 5th.

> SUM(LARGE(B14:I14,5))

This doesn't work - it returns the sum of the 5th-largest cell (i.e. the same as the value of the cell itself), not the sum of the five largest cell values.