Need help with sum formula to add x largest numbers

[mkoski]

I need help writing a formula to sum the six largest numbers from a row of numbers. I have used the array formula "=SUM(LARGE(E2:M2,{1,2,3,4,5,6}))" and it works for rows that have six or more numbers; however, not all of my rows always have six or more numbers. Rows that have less than six result in "#NUM!". Does anyone have suggestion to solve the problem?

Thank you for your help.

[zbor]

Here, try this (normal enter):

Please Login or Register  to view this content.

[popipipo]

=if(counta(E2:M2)<6,sum(e2:M2),SUM(LARGE(E2:M2,{1,2,3,4,5,6})))

Try this formula

[mkoski]

That works very well, thank you! Love this forum, always helpful answers.

[clabulis]

Your original formula will work if you force all cells in the range to have a number:

=SUM(LARGE(E2:M2+0,{1,2,3,4,5,6}))

Use ctrl+shift+enter to confirm the formula.

[newdoverman]

This seems to work

Please Login or Register  to view this content.

Oh, nuts....Excel 2003

Instead of SUMPRODUCT, SUM should work: (Array entered - Ctrl + Shift + Enter)

Please Login or Register  to view this content.