INDEX MATCH with COUNTIF?

[Tomeegee]

What I'm trying to do is combine INDEX and MATCH along with COUNTIF and not having an easy time of it.

In the full worksheet, Column B extends down 16 players. For each players performance, I want a formula that will Count the number of Eagles, Birdies, and Albratrosses achieved either "naturally" or "with handicap." You can see the values for those in P3:Q13. There's a basic COUNTIF formula currently in O6:T:6. You can see Player 6 scored 2 birdies with handicap, 1 eagle with handicap, and 1 albatross with handicap.

What I want to do is use an INDEX MATCH with the COUNTIF to match O3 to Column B, and then COUNTIF 13 rows below the name (Eagles, Birdies). So I can change Player 6 to Player 3 and the COUNTIF we deliver results from Row 33 and so on and so forth.

I've included a formula I was trying to use =INDEX(B1:B324,MATCH(O3,$B$1:$B$324,0)+13,COUNTIF($C1:$K322,Q11)) with no successs. How far off am I?

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Tomeegee]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2
3		Player 6													Player 6	=	COUNTIF($C16:$K16,Q11)
4
5															HB	HE	HA	NB	NE	NA
6															2	1	1	0	0	0
7		HOLE	1	2	3	4	5	6	7	8	9	Total
8		Score Card Par	5	4	3	4	4	3	5	4	4	36
9		Hole Handicap	9	13	15	7	3	17	1	11	5					Natural	With Hcp
10		Personal PAR	5	4	3	4	4	3	5	4	4	36			Junk Points
11		Score	4	4	5	4	6	4	3	7	5	42			Birdie	5	3
12		Net over/Under	-1	0	2	0	2	1	-2	3	1				Eagle	12	7
13															Albatross	25	15
14		Strokes V Course	6	5	4	5	5	3	6	5	5	44
15		Net over/Under	-2	-1	1	-1	1	1	-3	2	0
16		Eagle, Birdies	7	3		3			15			28
17															(formula pasted for demo purposes
18															#REF!
19
20		Player 3
21
22
23
24		HOLE	1	2	3	4	5	6	7	8	9	Total
25		Score Card Par	5	4	3	4	4	3	5	4	4	36
26		Hole Handicap	9	13	15	7	3	17	1	11	5
27		Personal PAR (strokes)	5	4	3	4	4	3	5	4	4	36
28		Score	6	4	7	4	6	7	5	6	4	49
29		Net over/Under	1	0	4	0	2	4	0	2	0
30
31		Strokes V Course	5	4	3	4	5	3	6	4	5	39
32		Net over/Under	1	0	4	0	1	4	-1	2	-1
33		Eagle, Birdies							3		3	6
34
35
36
37
Sheet1

Cell Formulas
Range	Formula
O6	O6	=COUNTIF($C16:$K16,Q11)
P6	P6	=COUNTIF($C16:$K16,Q12)
Q6	Q6	=COUNTIF($C16:$K16,Q13)
R6	R6	=COUNTIF($C16:$K16,P11)
S6	S6	=COUNTIF($C16:$K16,P12)
T6	T6	=COUNTIF($C16:$K16,P13)
O18	O18	=INDEX(B1:B324,MATCH(O3,$B$1:$B$324,0)+13,COUNTIF($C1:$K322,Q11))
B20	B20	='/Users/tomgoosmann/Documents/Personal/Golf Trips/Jekyll/Jekyll 2022/[Jekyll 2022 Scoring.xlsx]Tournment Matches '!C8

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C16:K16,C33:K33	Cell Value	contains "FALSE"	text	NO

 

[Tomeegee]

Hope that worked! Thanks for checking it out. Help means a lot.

 

[Fluff]

Thanks for that, how about

=COUNTIFS(INDEX($C$1:$K$324,MATCH(O3,$B$1:$B$324,0)+13,0),Q11)

 

[Tomeegee]

Thanks for that, how about

=COUNTIFS(INDEX($C$1:$K$324,MATCH(O3,$B$1:$B$324,0)+13,0),Q11)

Like a charm! Now to take it apart and learn from it! Looks like I had all the pieces parts but as usual not using them in proper order. Thank you thank you for taking the time!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Tomeegee]

Hello again Fluff! I am unsure what I've messed up. The above formula you solved for me has been giving me a #REF error and I can't deconstruct what I've done.

I've put together a sample file stripped down to just what the formula needs to do. XL2BB doesn't seem to carry over comments, so I'll explain:

On Scorecard I want INDEX Column B to MATCH the name on sheet "Tally"
On Scorecard I want to COUNTIF and matching criteria from S22 in Columns C:K. +13 lines down from the matched name.
Tally is where the formula I'm looking to fix goes (see screenshot).
S22 is where the Criteria to match and count is.

Make sense?

INDEX - MATCH - COUNTIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		Player
3		Jay M
4
5		FRONT
6
7		HOLE	1	2	3	4	5	6	7	8	9	Total
8		Score Card Par	4	3	5	4	4	3	5	4	4	36
9		Hole Handicap	11	17	1	13	7	15	3	9	5
10		Personal PAR	4	3	5	4	4	3	5	4	4	36
11		Score	4	4	5	4	6	4	3	7	5	42
12		Net over/Under	0	1	0	0	2	1	-2	3	1
13
14		Strokes V Course	4	3	6	4	4	3	6	4	5	39
15		Net over/Under	0	1	-1	0	2	1	-3	3	0
16		Eagle, Birdies			3				15			18
17		Sandie
18		Chippie
19
20
21		Player
22		Jim G
23
24		FRONT
25
26		HOLE	1	2	3	4	5	6	7	8	9	Total
27		Score Card Par	4	3	5	4	4	3	5	4	4	36
28		Hole Handicap	11	17	1	13	7	15	3	9	5
29		Personal PAR (strokes)	4	3	6	4	5	3	6	4	5	40
30		Score	6	4	7	4	6	7	5	6	4	49
31		Net over/Under	2	1	1	0	1	4	-1	2	-1
32
33		Strokes V Course	5	3	6	5	5	3	6	5	5	43
34		Net over/Under	1	1	1	-1	1	4	-1	1	-1
35		Eagle, Birdies				3			3		3	9
36
37
38
39
40		Player
41		Rob T
42
43		FRONT
44
45		HOLE	1	2	3	4	5	6	7	8	9	Total
46		Score Card Par	4	3	5	4	4	3	5	4	4	36
47		Hole Handicap	11	17	1	13	7	15	3	9	5
48		Personal PAR	4	3	5	4	4	3	5	4	4	36
49		Score	5	5	5	4	7	7	4	9	4	50
50		Net over/Under	1	2	0	0	3	4	-1	5	0
51
52		Strokes V Course	4	3	6	4	5	3	6	5	5	41
53		Net over/Under	1	2	-1	0	2	4	-2	4	-1
54		Eagle, Birdies			3				7		3	13
55		Sandie
56		Chippie
57
58
59		Player
60		Mike S
61
62		FRONT
63
64		HOLE	1	2	3	4	5	6	7	8	9	Total
65		Score Card Par	4	3	5	4	4	3	5	4	4	36
66		Hole Handicap	11	17	1	13	7	15	3	9	5
67		Personal PAR (strokes)	4	3	6	4	5	3	6	4	5	40
68		Score	8	5	6	8	8	6	7	7	4	59
69		Net over/Under	4	2	0	4	3	3	1	3	-1
70
71		Strokes V Course	5	4	6	5	5	4	6	5	5	45
72		Net over/Under	3	1	0	3	3	2	1	2	-1
73		Eagle, Birdies									3	3
74		Sandie
75		Chippie
76
77
78		Player
79		Brian A
80
81		FRONT
82
83		HOLE	1	2	3	4	5	6	7	8	9	Total
84		Score Card Par	4	3	5	4	4	3	5	4	4	36
85		Hole Handicap	11	17	1	13	7	15	3	9	5
86		Personal PAR	4	3	5	4	4	3	5	4	4	36
87		Score	5	3	8	4	7	5	6	6	5	49
88		Net over/Under	1	0	3	0	3	2	1	2	1
89
90		Strokes V Course	5	4	7	5	5	4	6	5	5	46
91		Net over/Under	0	-1	1	-1	2	1	0	1	0
92		Eagle, Birdies		3		3						6
93		Sandie
94		Chippie
95
96
97		Player
98		Ray L
99
100		FRONT
101
102		HOLE	1	2	3	4	5	6	7	8	9	Total
103		Score Card Par	4	3	5	4	4	3	5	4	4	36
104		Hole Handicap	11	17	1	13	7	15	3	9	5
105		Personal PAR (strokes)	4	3	6	4	5	3	6	4	5	40
106		Score	8	3	8	6	6	9	3	10	5	58
107		Net over/Under	4	0	2	2	1	6	-3	6	0
108
109		Strokes V Course	5	4	7	5	6	4	7	6	6	50
110		Net over/Under	3	-1	1	1	0	5	-4	4	-1
111		Eagle, Birdies		3							3	6
112		Sandie
113		Chippie
114
115
116		Player
117		Dean V
118
119		FRONT
120
121		HOLE	1	2	3	4	5	6	7	8	9	Total
122		Score Card Par	4	3	5	4	4	3	5	4	4	36
123		Hole Handicap	11	17	1	13	7	15	3	9	5
124		Personal PAR	4	3	5	4	4	3	5	4	4	36
125		Score	6	4	9	6	6	6	6	10	5	58
126		Net over/Under	2	1	4	2	2	3	1	6	1
127
128		Strokes V Course	5	3	6	5	5	3	6	5	5	43
129		Net over/Under	1	1	3	1	1	3	0	5	0
130		Eagle, Birdies										0
131		Sandie
132		Chippie
133
134
135		Player
136		Ken R
137
138		FRONT
139
140		HOLE	1	2	3	4	5	6	7	8	9	Total
141		Score Card Par	4	3	5	4	4	3	5	4	4	36
142		Hole Handicap	11	17	1	13	7	15	3	9	5
143		Personal PAR (strokes)	4	3	6	4	4	3	5	4	4	37
144		Score	7	4	7	9	6	8	3	8	3	55
145		Net over/Under	3	1	1	5	2	5	-2	4	-1
146
147		Strokes V Course	5	3	6	5	5	4	6	5	5	44
148		Net over/Under	2	1	1	4	1	4	-3	3	-2
149		Eagle, Birdies							15		7	22
150		Sandie
151		Chippie
152
153
154
155
156
Scorecard

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C149:K149	Cell Value	contains "FALSE"	text	NO
C130:K130	Cell Value	contains "FALSE"	text	NO
C111:K111	Cell Value	contains "FALSE"	text	NO
C92:K92	Cell Value	contains "FALSE"	text	NO
C73:K73	Cell Value	contains "FALSE"	text	NO
C54:K54	Cell Value	contains "FALSE"	text	NO
C16:K16,C35:K35	Cell Value	contains "FALSE"	text	NO

and this

INDEX - MATCH - COUNTIF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	Column1	Column2	Front/Back	HB	HE	HA	NB	NE	NA
2	Ken R	IndianMound	FRONT
3		IndianMound	BACK
4	Skins	Oleander	FRONT
5		KingPrince	FRONT
6		KingPrince	BACK
7		Oleander	FRONT
8		Oleander	BACK
9	Skins	PineLakes	BACK
10		PineLakes	FRONT
11		PineLakes	BACK
12
13	Totals			0	0	0	0	0	0
14
15											=	INDEX('Scorecard'!B1:B324,MATCH(A2,'Scorecard'!$B$1:$B$324,0)+13,COUNTIF('Scorecard'!$C1:$K322,'S22'!C3))
16	Jim G	IndianMound	FRONT
17		IndianMound	BACK
18	Skins	Oleander	FRONT
19		KingPrince	FRONT
20		KingPrince	BACK
21		Oleander	FRONT
22		Oleander	BACK
23	Skins	PineLakes	BACK
24		PineLakes	FRONT
25		PineLakes	BACK
26
27	Totals			0	0	0	0	0	0
28
29	Ray L	IndianMound	FRONT
30		IndianMound	BACK
31	Skins	Oleander	FRONT
32		KingPrince	FRONT
33		KingPrince	BACK
34		Oleander	FRONT
35		Oleander	BACK
36	Skins	PineLakes	BACK
37		PineLakes	FRONT
38		PineLakes	BACK
39
40	Totals			0	0	0	0	0	0
Tally

and this

INDEX - MATCH - COUNTIF.xlsx
	A	B	C	D	E	F
1		Natural (N)	Handicap (H)
2	Score
3	Birdie (B)	5	3
4	Eagle (E)	12	7
5	Albatross (A)	25	15
6
7
8
9
10
11
12
13
14
15
16
S22

 

[Fluff]

The formula you have shown is not the same as the formula I posted.

 

[Tomeegee]

I'm searching desperately for the "I'm such a dope" emoji. When you said that I went "back to base camp to look for a map" and saw I wasn't working with the last formula you provided. Thanks for taking the time and setting me straight!