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))

 

[hagia_sofia]

Hello, would this work for you:

=LET(
a,FILTER(B4:F4,B4:F4<>0),
b,SEQUENCE(,IF(COLUMNS(a)<4,COLUMNS(a),4)),
SUM(SMALL(a,b)))

 

[RoryA]

What do you want to do if they played fewer than the 4 rounds? Given that lowest score is best, it doesn't really seem fair to include players who only have say 3 rounds as they will (probably!) have a lower total.

 

[Scott Huish]

Adding a 0 and summing nothing is exactly the same. What are you expecting the result to be?
BTW, I think you could change your formula to this:
=SUM(SMALL(B4:F4,{1,2,3,4}))

I don't have older versions of Excel anymore, you may need CTRL-SHIFT-ENTER for that.

 

[RoryA]

you may need CTRL-SHIFT-ENTER for that

No, because it's an array constant.

 

[Scott Huish]

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

 

[GMC The Macro Man]

What do you want to do if they played fewer than the 4 rounds? Given that lowest score is best, it doesn't really seem fair to include players who only have say 3 rounds as they will (probably!) have a lower total.

Hi RoryA,
You make a fair point here but as there are only 20 players in our golf section we have always been able to manage this manually. There are 5 outings and they all have a lot of manual input over several pages so I wanted to put formulas where possible to make it easier.
My initial plan was to put conditional formatting on anyone who had a "0", and make that cell RED. A quick scan would allow you to see who had more than two zero's and would therefore not be included but It would be better if the TOTAL column could calculate the best FOUR of the FIVE rounds and then SORT in order. That's a calculation for another day then

 

[RoryA]

You could amend the Best 4 formula to return something else instead, like "Not enough rounds" rather than an actual score?

 

[RoryA]

For example:

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

 

[GMC The Macro Man]

Hello, would this work for you:

=LET(
a,FILTER(B4:F4,B4:F4<>0),
b,SEQUENCE(,IF(COLUMNS(a)<4,COLUMNS(a),4)),
SUM(SMALL(a,b)))

Hey there. 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