Sum highest n values in a row

[Hawklet]

I would appreciate some help as I have tried a number of variations (LARGE, SUMPRODUCT, RANK) but nothing is working. Keep getting an error message on the array in the LARGE formula.

I have a row of 33 numbers (G4:AM4) and need to sum the highest 22 numbers in that range. I do not think RANK will work as some of the numbers are the same value and I need the highest 22 values.

I have tried
=SUM(LARGE(G4:AM4,ROW(INDIRECT("1:22")
and also
=SUM(IF(COUNT(G4:Z4)<10,G4:Z4,LARGE(G4:Z4,{1,2,3,4,5,6,7,8,9,10})))

Thanks
Bev

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(LARGE(G4:AM4,ROW(INDIRECT("1:"&MIN(COUNT(G4:AM4),22)))))

 

[VBA Geek]

=sumproduct(aggregate(14;6;$g$4:$am$4;row($a$1:$a$22)))



if you want to make it dynamic and change the number of highest numbers you wish to sum up then assuming your have your 22 in B1 then:
=IF(B1>COUNTA($G$4:$AM$4);"";SUMPRODUCT(AGGREGATE(14;6;$G$4:$AM$4;ROW(INDIRECT("1:" &B1)))))

 

[Hawklet]

Thanks Aladin for the reply.

Tried this formula again but still get and error message. Did use Control+shift+enter previous as well.

 

[Hawklet]

Thanks VBA Geek.

The sumproduct formula works but the problem is if there are less than 22 values in the row, I then get a #NUM error.

I need the values to be added up to a maximum of 22 - if there are only 12 values, I still need them to be added.

 

[Aladin Akyurek]

Thanks Aladin for the reply.

Tried this formula again but still get and error message. Did use Control+shift+enter previous as well.

What is the error message?

 

[VBA Geek]

=SUMPRODUCT(IFERROR(AGGREGATE(14;6;G4:AM4;ROW(A1:A22));0))

enter with CTRL SHIFT ENTER

 

[Hawklet]

Aladin
It is just "The formula you typed contained an error and it shows the array in "Large" as being invalid.

 

[Hawklet]

Thanks VBA Geek.

 

[Aladin Akyurek]

Aladin
It is just "The formula you typed contained an error and it shows the array in "Large" as being invalid.

Probably a paren is missing or too many... It should be:

=SUM(LARGE(G4:AM4,ROW(INDIRECT("1:"&MIN(COUNT(G4:AM4),22)))))

confirmed with control+shift+enter.