Using =SUM(SMALL to count but omitting Zero

[GMC The Macro Man]

Hey everyone,

I'm doing a small spreadsheet for our local golf group and I'm stuck at using the =SUM(SMALL formula.
The golfers play FIVE rounds and the calculation is their BEST FOUR scores from the five. In golf, it's the LOWEST score that counts so I wrote this formula
=SUM(SMALL(B4:F4,1)+SMALL(B4:F4,2)+SMALL(B4:F4,3)+SMALL(B4:F4,4))

This works great if the golfer attended all five outings and returned a score, however, if they missed an outing, then a ZERO is recorded. This throws a wobbly as the formula counts the zero as one of the lowest scores.
How can i amend the formula not to count zero's. I tried this but it gave an error
=SUMIF(SMALL(B4:F4,"<>0",1)+SMALL(B4:F4,"<>0",2)+SMALL(B4:F4,"<>0",3)+SMALL(B4:F4,"<>0",4))

Thanks as always
GMC

Best Golf Rounds.xlsx
	A	B	C	D	E	F	G
1			2024 CLUB Championship
2
3	PLAYER	KINGS ACRE	LOUDOUN GC	AUCHTERARDER	HIRSEL	THORNTON	BEST 4 RDS
4	Player 1	63	73	69	68	69	269
5	Player 2	68	67	0	75	0	135
6	Player 3	68	70	75	72	70	280
7	Player 4	70	70	0	76	72	212
8	Player 5	72	76	0	81	68	216
9	Player 6	75	73	71	0	0	144
10	Player 7	76	79	0	78	0	154
11	Player 8	78	84	0	84	70	232
12	Player 9	79	0	77	80	72	228
13	Player 10	80	73	85	76	0	229
14	Player 11	84	73	82	87	86	325
15	Player 12	80	73	83	81	0	234
16	Player 13	82	82	85	75	81	320
17	Player 14	92	79	76	81	0	236
18	Player 15	86	83	80	75	71	309
19	Player 16	84	84	80	0	81	245
20	Player 17	97	0	0	89	89	178
21	Player 18	0	81	80	0	0	80
22	Player 19	0	83	81	0	0	81
23	Player 20	0	69	74	0	0	69
Sheet1

Cell Formulas
Range	Formula
G4:G23	G4	=SUM(SMALL(B4:F4,1)+SMALL(B4:F4,2)+SMALL(B4:F4,3)+SMALL(B4:F4,4))

 

[GMC The Macro Man]

Actually, since there's only 5, you could also SUM them and subtract out the largest one:
=SUM(B4:F4,-MAX(B4:F4))

Hi Scott
Many thanks for looking at my question and giving me your solution. I really appreciate you taking the time to do this.
Thanks again
GMC

 

[GMC The Macro Man]

For example:

=IF(COUNTIF(B4:F4,">0")<4,"Not enough rounds",SUM(SMALL(B4:F4,{1,2,3,4})))

Hi RoryA
Many thanks for this solution. I will try this when I get back home tonight.
All the best
GMC