sumproduct with 2 vertical columns

[rangelsammon]

If anyone could assist. this would be awesome. i have been stuck on this problem for 2 days. cant figure it out. i think i might need a "helper" column but i want to try to avoid and dont want to use offset columns by numbers.

link to sheet too for editing

Book1.xlsx

Microsoft Excel Workbook

1drv.ms

count if

if a column J (Win) cell contains "yes"

then check column D (inning) and see which inning that "yes" occurred

if column F (ball in hand) contains "yes"

then check column D (inning) and see which inning that "yes" occurred

if those 2 occurred in the same inning then count

only count if happened by the same player (column C) & if in the same match (column A)

to the left the answer should be 1 for john

but if I change cell b9 to 2 then the answer should be 2 for john

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​		rack	player	inning	safety	ball in hand	made	miss	foul	win
2​	4​	2​	john	2​			yes			yes
3​	4​	2​	john	2​			yes
4​	4​	2​	john	2​		yes	yes
5​	4​	2​	rick	1.5​					yes
6​	4​	2​	john	1​	yes
7​	4​	2​	rick	0.5​	yes
8​	4​	2​	john	0​				yes
9​	4​	1​	john	2​		yes				yes
10​	4​	1​	rick	1.5​					yes
11​	4​	1​	john	1​	yes

 

[rangelsammon]

heres my attempt at it as this formula may explain better
=SUMPRODUCT((data[match]=4)*(data[player]=Player1)*(data[win]="yes")*(data[ball in hand]="yes")*(data[inning]=INDEX(data[inning],MATCH("yes",data[ball in hand]))=INDEX(data[inning],MATCH("yes",data[win]))))

whenever the inning matches the yes in column win & column ball in hand then that is true and therefore count how many times this happens during a match by a player

 

[rangelsammon]

heres my attempt at it as this formula may explain better
=SUMPRODUCT((data[match]=4)*(data[player]=Player1)*(data[win]="yes")*(data[ball in hand]="yes")*(data[inning]=INDEX(data[inning],MATCH("yes",data[ball in hand]))=INDEX(data[inning],MATCH("yes",data[win]))))

whenever the inning matches the yes in column win & column ball in hand then that is true and therefore count how many times this happens during a match by a player

player1 is john

 

[rangelsammon]

Unfortunately this is far more complex than I thought (unless I'm overthinking it), so not sure I can supply an answer.
Hopefully somebody else can figure it out.

thank you again for reaching out