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

 

[Fluff]

Hi & welcome to MrExcel.
Why would the answer change if B9 changed, that column does exist in your criteria.

 

[rangelsammon]

Hi & welcome to MrExcel.
Why would the answer change if B9 changed, that column does exist in your criteria.

oh sorry, didnt explain well. meaning if you changed b9 to 2. then the answer would be 2. that is 2 times the above criteria would be met. thats my way of seeing if the formula works. i edited my post to explain that. thanks for the heads up

 

[rangelsammon]

well i thought i could edit but here it is again below worded better

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 check the formula the answer should be 1 for john

but if I change cell b9 under column B (inning) to 2 then the answer should be 2 for john because the other win after ball in hand happened in inning 2 as did the ball in hand.

 

[Fluff]

But col B is not mentioned in any of your criteria, so I don't see how that should affect the result.

 

[rangelsammon]

But col B is not mentioned in any of your criteria, so I don't see how that should affect the result.

ha! i just noticed lol oh man. first post. i tried to get get perfect. i meant column D. alright. maybe last time i edit. i hope

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 d9 to 2 then the answer should be 2 for john

 

[Fluff]

Should the "Rack" be taken into consideration?

 

[rangelsammon]

no, just counting per match. not rack per match. but essentially you are counting how many racks this happened in during the match.

 

[rangelsammon]

im sorry fluff. im all over the place.

it should be if d9 changes to 1. then the answer is 1. but answer is 2 if d9 stays 2.

 

[Fluff]

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.