SOLVED

New Contributor

# Excel Help! - Countif combined with index match?

book13.xlsx    example here

I am trying to figure out a billiard question. see sheet for breakdown. i think it needs a combination of sumproduct and index match. or maybe sumproduct and countifs. ive tried different ways but i cant figure it out

3 Replies
best response confirmed by rangelsammon (New Contributor)
Solution

# Re: Excel Help! - Countif combined with index match?

Try this:

=SUMPRODUCT((B2:B5=H1:K1)*(H2:K5="top left"))

# Re: Excel Help! - Countif combined with index match?

i thought it was good but i am actually missing a few. i really want to thank you for taking the time to assist. do you mind one more time? i made it more defined. i added the match racks. labeled a2-a5. and a better explaination

in the first rack A2 the player racks the 1 ball in the high left and the ball ends up in the top left of the table after the break. So the answer for how many times the high left ball ended in the top left after the break is 1. but now i want to know how many times player A did it. the result should be 2

is there a way to use columns such as A:A for a range vs A2:A5?

https://1drv.ms/x/s!AnFi6uGE1rekiUPZG4iE1vFh6Gza?e=Vu3gES

# Re: Excel Help! - Countif combined with index match?

i think i figured it out
=SUMPRODUCT((A2:A5=1)*(B2:B5="a")*(C2:C5=3)*(I1:L1=C2:C5)*(I2:L5="bottom left"))