# Sum highest n values in a row

#### Hawklet

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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Control+shift+enter, not just enter:

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

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

Last edited:

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

Last edited:
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.

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

What is the error message?

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

enter with CTRL SHIFT ENTER

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

Thanks VBA Geek.

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.

Replies
7
Views
326
Replies
1
Views
165
Replies
1
Views
172
Replies
3
Views
280
Replies
4
Views
194

1,203,762
Messages
6,057,218
Members
444,914
Latest member
Mamun12345

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