Disregarding cells that are not in the top 4 and totaling the disregarded cell

[ORIELSON]

Hi

i have 10 columns. The first and the 4 highest are added to give me a total. However anything after 4 must be disregarded. I am using the formula
=IF(COUNT(H74:Q74)>4,MIN(H74:Q74),0) (which was suggested to me last year on this forum), this gets rid of the 5th lowest number in the range. However I need to disregard all cells other the 4 highest.

Any ideas
Thanks

[BadlySpelledBuoy]

I'm not sure I'm understanding this one.
Could you post some sample data with a few examples of which values you wish to sum and which ones you wish to disregard?

BSB

[JohnTopley]

This will sum the 4th largest

=SUM(LARGE(H1:Q1,{1,2,3,4}))

Enter with Ctrl+Shift+enter

[ORIELSON]

Q.1. Q.2. Q.3. Q.4. Q.5. Q.6. Q.7. Q.8. Q.9. Q.10. Q.10.A Exclude Exam # Total
45 50 28 33 29 14 14
21 39 14 4)27 12
52 36 43 34 16 56 37 16

In the exclude column, I need to add Q1 and the next 4 highest and then record the cell that is not included. Ex row 1 excludes 14(Q10). But the third row only record 16 under exclude whereas it shoud include 16 and 34(Q4)

Thanks

[ORIELSON]

Hi

Q.1. Q.2. Q.3. Q.4. Q.5. Q.6. Q.7. Q.8. Q.9. Q.10. Q.10.A Exclude Exam # Total
45 50 28 33 29 14 14
21 39 14 4)27 12
52 36 43 34 16 56 37 16

In the exclude column, I need to add Q1 and the next 4 highest and then record the cell that is not included. Ex row 1 excludes 14(Q10). But the third row only record 16 under exclude whereas it shoud include 16 and 34(Q4)

The formula that I am using at the moment is:=IF(COUNT(H69:Q69)>4,MIN(H69:Q69),0)

Thanks

[JohnTopley]

See attached as possible solution

[ORIELSON]

Thanks John

That seems to work. Is there anyway that i can place the same excluded numbers in the one cell.

Thanks again
Frank

[JohnTopley]

There is no easy way in Excel. You could put the "Extraction" formula in (hidden) "helper" columns (say AA to AI) and then use CONCATENATE(AA2 & AB2 & AC2 ...).