sum of six highest value within a range

[melnexcel]

I have following values in cells:
S4: 846
T4: 846
U4: 846
V4: 893
W4:893
X4: 893
Y4: 893
Z4: 0
AA4: 0
AB4: 0
AC4: 0
AD4: 0

I need to sum the six highest value within those cells. I expect to get a sum of 5264, but does not know how to create a formula for this. I tried using SUMPRODUCT function but this always return an error in Excel 2010.

Thank you very much for any help.

 

[RedBeard]

This might be of help: https://exceljet.net/formula/sum-top-n-values

 

[joeu2004]

No need to use SUMPRODUCT in this case. The following normally-entered formula is sufficient:

=SUM(LARGE(S4:AD4,{1,2,3,4,5,6}))

Because of the array constant {1,...,6}, Excel is able to recognize that as an array formula without the need to array-enter the SUM formula (press ctrl+shift+Enter) or to use SUMPRODUCT.

 

[melnexcel]

Whenever I try to write the formula: =SUM(LARGE(S4:AD4,{1,2,3,4,5,6}))
Excel always says: The formula you typed contains an error and "AD4" is highlighted. Don't know what is wrong with it. I am using Excel 2010 if it may help.

The same is true if I use the formula: =SUMPRODUCT(LARGE(S4:AD4,{1,2,3,4,5,6}))

 

[FormR]

Excel always says: The formula you typed contains an error and "AD4" is highlighted.

Hi, your system probably uses a comma as the decimal separator, in which case you need to replace them in the formula with semi-colons, i.e.

=SUM(LARGE(S4:AD4;{1;2;3;4;5;6}))

Note, this assumes you will always have at least 6 values in the range to sum.