Golf score sheet

[michaelt721]

I run a golf league and have a score sheet in excel to help keep track of points won each week

I am looking for a formula that will look back at all weeks played (not counting the blank cells) and drop the lowest 2, then sum the rest.


Example:


--A-------B-------C--------D------E--------F--------G-------H--------I-----...........P---Q-----R----------S
name week1 week2 week3 week4 week 5 week6 week 7 ------------------------Total----Adj total
Mike-----7-------4--------3-------9------11--------0-------13----------------------------------47--------44

The total is 7+4+3+9+ 11+0+13=47
However after dropping the 2 lowest (including zero's) the total should be 7+4+9+ 11+13=44
So what would the formula be for Column S?

We golf 16 weeks, so I'd like the formula to include all 16 weeks so I don't have to keep adjusting the formula each week.
In the weeks not yet played, the cells would be empty.

Thank you in advance!

 

[ETAF]

try using
=SUM(B3:Q3)-SMALL(IF(B3:Q3=0,"",B3:Q3),1)-SMALL(IF(B3:Q3=0,"",B3:Q3),2)

ignore - i thought you excluded 0

so
=SUM(B3:Q3)-SMALL(B3:Q3,1)-SMALL(B3:Q3,2)

see row 3 in attached


16 weeks is b to q

 

[bosco_yip]

Or,

=SUM(B3:Q3,-SMALL(B3:Q3,{1,2}))