Solution for sum of cells with inclusion of mandatory cells

[OmahaBassAngler]

Was very happy to find this forum and hoping to find a Excel wizard....


I am using Excel to keep track of results for a fishing league. As such I am trying to get the formula that will add the seven largest numbers entered in a row/column. However, this sum MUST contain at least one score entry from one of three mandatory scoring colums. Further explained, we have 11 tournements and only your top 7 scores count. However 3 tournements are considered mandatory. Therefore, one of your top seven scores must be the result of one of those mandatory three. You could have all three mandatory scores in your top 7 but at a minimum you must have one.

Probably over-explained but better more than not enough. So far I have:

"=LARGE(D5:N5,7)+LARGE(D5:N5,6)+LARGE(D5:N5,5)+LARGE(D5:N5,4)+LARGE(D5:N5,3)+LARGE(D5:N5,2)+LARGE(D5:N5,1)"

The mandatory cells are F5, G5, and N5. So one of the top seven scores must come from these three cells.

I think this is correct for totaling the seven best scores. However, I need to find the additonal formula for ensuring that at least one of those numbers comes from three mandatory cells.

Hoping for help!

Thanks,
OBA

[DonkeyOte]

Does the below work for you ?

=SUM(LARGE(D5:N5,{1,2,3,4,5,6,7}))-MAX(0,LARGE(D5:N5,7)-MAX(F5,G5,N5))

[OmahaBassAngler]

Does the below work for you ?

=SUM(LARGE(D5:N5,{1,2,3,4,5,6,7}))-MAX(0,LARGE(D5:N5,7)-MAX(F5,G5,N5))

DonkeyOte,

That almost works. While it is providing the sum, I am looking for a zero score if the entries to the three mandatory fields are zero.

So, if F5, G5, F5 are "0", then sum="0".

Thanks for the formula.
OBA

[DonkeyOte]

Add a SUM test

Please Login or Register  to view this content.

[OmahaBassAngler]

Actually, that is correct. Thanks DonkeOte. I really appreciate the help.

OBA