GOLF EXCEL COUNTING CARRIED SKINS THAT COME BEFORE EACH NAME.

[kameron1967]

Hi. I wanted to create a spreadsheet that automatically count the number of TIE above each name. I explained a little bit on the spreadsheet, so if there is a tie on a hole, it is carried to the next hole until someone wins that hole, which include all the TIE that were not won. SAL/BAILEY only wins 6 carried skins for holes 3/4/5/6/7/8, because ENRICO won the TIE on hole 1. Thank you in advance.

TOTAL PLAYERS		60​
$ PER PLAYER		$18​
TOTAL MONEY PRIZE		$1,080​
TOTAL SKINS - NO CARRY	8​
HOLE	CARRY - Y/N	HOLE WINNER
#1	YES	TIE	This skin is carried to hole #2 and ENRICO won it.
#2		ENRICO
#3	YES	TIE
#4	YES	TIE
#5	YES	TIE
#6	YES	TIE
#7	YES	TIE
#8	YES	TIE
#9		SAL/BAILEY	These skins are carried to hole #9 and SA/BAILEY won them.
#10		ZACH/JESSE
#11	YES	TIE
#12		BLAZER (BOUN)	This skin is carried to hole #12 and BLAZER (BOUN) won it.
#13		Buun-KGA
#14		ELIJAH
#15		ALLAN/DANNY
#16	YES	TIE	This skin is carried to hole #16 and Pounky-KGA won it.
#17		Pounky-KGA
#18	YES	TIE	This skin is tied on the last hole, so no one gets this skin.

 

[JamesCanale]

This needs xmatch to work - I don't know what version you are running:

MrExcelPlayground12.xlsx
	A	B	C	D	E
2	HOLE	CARRY - Y/N	HOLE WINNER	Skins
3	#1	YES	TIE		This skin is carried to hole #2 and ENRICO won it.
4	#2		ENRICO	2
5	#3	YES	TIE
6	#4	YES	TIE
7	#5	YES	TIE
8	#6	YES	TIE
9	#7	YES	TIE
10	#8	YES	TIE
11	#9		SAL/BAILEY	7	These skins are carried to hole #9 and SA/BAILEY won them.
12	#10		ZACH/JESSE	1
13	#11	YES	TIE
14	#12		BLAZER (BOUN)	2	This skin is carried to hole #12 and BLAZER (BOUN) won it.
15	#13		Buun-KGA	1
16	#14		ELIJAH	1
17	#15		ALLAN/DANNY	1
18	#16	YES	TIE		This skin is carried to hole #16 and Pounky-KGA won it.
19	#17		Pounky-KGA	2
20	#18	YES	TIE		This skin is tied on the last hole, so no one gets this skin.
Sheet20

Cell Formulas
Range	Formula
D3	D3	=IF(C3="TIE","",1)
D4:D20	D4	=IF(C4="TIE","",1+ROWS(C$3:C3)-IFNA(XMATCH(FALSE,C$3:C3="TIE",0,-1),0))

 

[kameron1967]

This needs xmatch to work - I don't know what version you are running:

Thank you James. You're a savior! Could you break down that XMATCH formula so I can understand?

 

[JamesCanale]

I convert everything above the row in question into a series of TRUE and FALSEs (the C$3:C#="TIE" creates an array of TRUE and FALSE). I search that array using XMATCH (which allows for a bottom up search - unlike MATCH), to find the last FALSE (instead of the first). So, the ninth hole has 8 records above it. I subtract the row of the last FALSE (2nd row), and add one (for the ninth hole), to get 7.

So XMATCH looks for FALSE, in the newly created array (C$3:C#="TIE") and finds the last one (used the -1 parameter at the end of XMATCH), and subtracted that from the total rows of C$3:C#.

 

[kameron1967]

What if ENRICO also won two more skins further down (hole #17), what is the formula for adding all the skins that he won for all the 18 holes..? Thanks in advance.

 

[JamesCanale]

You'd do this:

MrExcelPlayground12.xlsx
	A	B	C	D
2	HOLE	CARRY - Y/N	HOLE WINNER	Skins
3	#1	YES	TIE
4	#2		ENRICO	2
5	#3	YES	TIE
6	#4	YES	TIE
7	#5	YES	TIE
8	#6	YES	TIE
9	#7	YES	TIE
10	#8	YES	TIE
11	#9		SAL/BAILEY	7
12	#10		ZACH/JESSE	1
13	#11	YES	TIE
14	#12		BLAZER (BOUN)	2
15	#13		Buun-KGA	1
16	#14		ELIJAH	1
17	#15		ALLAN/DANNY	1
18	#16	YES	TIE
19	#17		ENRICO	2
20	#18	YES	TIE
21
22
23			Totals
24			ENRICO	4
25			SAL/BAILEY	7
26			ZACH/JESSE	1
27			BLAZER (BOUN)	2
28			Buun-KGA	1
29			ELIJAH	1
30			ALLAN/DANNY	1
Sheet20

Cell Formulas
Range	Formula
D3	D3	=IF(C3="TIE","",1)
D4:D20	D4	=IF(C4="TIE","",1+ROWS(C$3:C3)-IFNA(XMATCH(FALSE,C$3:C3="TIE",0,-1),0))
C24:C30	C24	=FILTER(UNIQUE(C3:C20),UNIQUE(C3:C20)<>"TIE")
D24:D30	D24	=SUMIFS(D3:D20,C3:C20,C24#)
Dynamic array formulas.

or this:

MrExcelPlayground12.xlsx
	A	B	C	D	E
2	HOLE	CARRY - Y/N	HOLE WINNER	Skins	Cumulative Skins
3	#1	YES	TIE
4	#2		ENRICO	2	2
5	#3	YES	TIE
6	#4	YES	TIE
7	#5	YES	TIE
8	#6	YES	TIE
9	#7	YES	TIE
10	#8	YES	TIE
11	#9		SAL/BAILEY	7	7
12	#10		ZACH/JESSE	1	1
13	#11	YES	TIE
14	#12		BLAZER (BOUN)	2	2
15	#13		SAL/BAILEY	1	8
16	#14		ELIJAH	1	1
17	#15		ALLAN/DANNY	1	1
18	#16	YES	TIE
19	#17		ENRICO	2	4
20	#18	YES	TIE
Sheet20

Cell Formulas
Range	Formula
D3	D3	=IF(C3="TIE","",1)
E3	E3	=D3
D4:D20	D4	=IF(C4="TIE","",1+ROWS(C$3:C3)-IFNA(XMATCH(FALSE,C$3:C3="TIE",0,-1),0))
E4:E20	E4	=IF(D4<>"",D4+SUMIFS(D$3:D3,C$3:C3,C4),"")

 

[kameron1967]

I was able to use the vlookup function to do the work, but I'll use yours instead though. Love the first option! Thanks again, James!