Extract top 4 values and sum

[eugbed]

I would like help combining the formulas in column R-U, plus column V (which is just the value in column P). Essentially I am trying to sum the 4 largest values from Column A-O plus the value in column P. As you can see I am also running into problems when there are less than 4 values (still need to sum them plus value in column P, even if less than 4 values in A-O.
Thank you!!

[Naren_Kumar]

Used below formula to arrive at the result:

=IFERROR(LARGE($A3:$O3,COLUMN(A$1)),0)

Please see attachment for details

[Haluk]

Alternative;

Enter the below formula in cell W3 and fill down...

Please Login or Register  to view this content.

[BMV]

Array formula

Please Login or Register  to view this content.

[eugbed]

Thank you for quick replies - I will try each and report back (at work now)

[eugbed]

Still having some problems with this - but getting close - thank you for responses. I have tried each. Can I achieve the correct answer (as in column W highlighted in green) with one overall formula, that sums the 4 highest values from column A-O, plus the value in column P, and corrects for the errors when there are less than 4 values in range A:O? I have attached another file - thank you!!

Having trouble attaching new file - hopefully this question will be clear enough. Will continue to try and attach new version

[FlameRetired]

eugbed your profile shows Excel 2003 yet the uploads are in formats newer. Your profile seems unlikely.

Members tailor answers based on your Excel version(s). Please update your profile.
Thanks

That said try this in cell W3 and fill down. If it returns errors or clearly wrong answers then try array entering in W3 and filling down.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[BMV]

eugbed your profile shows Excel 2003 yet the uploads are in formats newer.

Office 2007 compatibility pack allow to use new file format.

eugbed, There are two versions in the file. From #4 and for 2003

Please Login or Register  to view this content.

[FlameRetired]

My phrasing was poor. Sorry about that. I should have said " ... yet your (the OP's) uploads are in formats newer."

[eugbed]

Thank you - have edited profile (has been awhile since I have been active on this site!). Will try your suggestions and report back

[eugbed]

I have gone back and looked at all of these again, and I think this is one I can work with for now (from BMV) - thank you all for your patience - I was getting quite confused!!
Thank you BMV
I am going to have to learn more about the Control-shift-enter for entering array formulas on my MAC - could not quite master that FlameRetired...

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[Pepe Le Mokko]

Just be aware that array formulas will eventually slow your sheets down...

[BMV]

Just be aware that array formulas will eventually slow your sheets down...

Can you prove it and exact for this case?
For 50000 rows result of calculation (sec)
0,203125 Non array
0,171875 Array1
0,167968 Array2
and calc again
0,1875 Non array
0,171875 Array1
0,171875 Array2

for 200000 rows
0.515625 Non array
0.40625 Array1