Computing Golf Skins Earned

[ptmuldoon]

I am trying to determine the total number of holes/skins a player wins. I've already worked out using SumProduct and Minifs to determine what particular hole a skin is won. But I'm trying to have that also include the previous holes tied.

In the example attached and working with Week 1, I have a skin won on Hole #3. Since there was no skin won on holes 1 and 2, It should award 3 skins.

The issue gets a little more complex as you then want to restart that count on next hole.

So for Week 1, the end result would be:

Hole #3: 3 skins won
Hole #4: 1 skin
Hole #7: 3 skins
Hole #9: 2 skins

Sample attached that has 20 weeks of scores, and on the right side you can see where it currently checks and finds what hole the skin was won.

[Bo_Ry]

Please try at
S6
=LET(ws,$D$6:$D$405,w,$Q6,s,F$6:F$405,c,R6:$S6,
IF(COUNTIFS(ws,w,s,MINIFS(s,ws,w))=1,IF(COLUMNS($S6:S6)=1,1,COLUMNS(c)-IFNA(XMATCH(TRUE,c<>0,0,-1),0)+1),0))


R6
=SUM(S6:AA6)

[ptmuldoon]

Please try at
S6
=LET(ws,$D$6:$D$405,w,$Q6,s,F$6:F$405,c,R6:$S6,
IF(COUNTIFS(ws,w,s,MINIFS(s,ws,w))=1,IF(COLUMNS($S6:S6)=1,1,COLUMNS(c)-IFNA(XMATCH(TRUE,c<>0,0,-1),0)+1),0))


R6
=SUM(S6:AA6)

That worked perfectly. I've read a little bit about the Let function but haven't had a chance to really digest and use it yet. Seeing it use here, I can clearly see how it does make a formula look cleaner too.

And fortunately I'm using Office 365.