SOLVED

Excel Help! - Countif combined with index match?

%3CLINGO-SUB%20id%3D%22lingo-sub-2705597%22%20slang%3D%22en-US%22%3EExcel%20Help!%20-%20Countif%20combined%20with%20index%20match%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2705597%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AnFi6uGE1rekiUGypNRIod58DSLP%3Fe%3DGDMvHa%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ebook13.xlsx%26nbsp%3B%20%26nbsp%3B%20example%20here%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20figure%20out%20a%20billiard%20question.%20see%20sheet%20for%20breakdown.%20i%20think%20it%20needs%20a%20combination%20of%20sumproduct%20and%20index%20match.%20or%20maybe%20sumproduct%20and%20countifs.%20ive%20tried%20different%20ways%20but%20i%20cant%20figure%20it%20out%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2705597%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2706338%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help!%20-%20Countif%20combined%20with%20index%20match%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2706338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1143050%22%20target%3D%22_blank%22%3E%40rangelsammon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUMPRODUCT((B2%3AB5%3DH1%3AK1)*(H2%3AK5%3D%22top%20left%22))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2710008%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Help!%20-%20Countif%20combined%20with%20index%20match%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2710008%22%20slang%3D%22en-US%22%3Ei%20thought%20it%20was%20good%20but%20i%20am%20actually%20missing%20a%20few.%20i%20really%20want%20to%20thank%20you%20for%20taking%20the%20time%20to%20assist.%20do%20you%20mind%20one%20more%20time%3F%20i%20made%20it%20more%20defined.%20i%20added%20the%20match%20racks.%20labeled%20a2-a5.%20and%20a%20better%20explaination%3CBR%20%2F%3E%3CBR%20%2F%3Ein%20the%20first%20rack%20A2%20the%20player%20racks%20the%201%20ball%20in%20the%20high%20left%20and%20the%20ball%20ends%20up%20in%20the%20top%20left%20of%20the%20table%20after%20the%20break.%20So%20the%20answer%20for%20how%20many%20times%20the%20high%20left%20ball%20ended%20in%20the%20top%20left%20after%20the%20break%20is%201.%20but%20now%20i%20want%20to%20know%20how%20many%20times%20player%20A%20did%20it.%20the%20result%20should%20be%202%3CBR%20%2F%3E%3CBR%20%2F%3Eis%20there%20a%20way%20to%20use%20columns%20such%20as%20A%3AA%20for%20a%20range%20vs%20A2%3AA5%3F%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2F1drv.ms%2Fx%2Fs!AnFi6uGE1rekiUPZG4iE1vFh6Gza%3Fe%3DVu3gES%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2F1drv.ms%2Fx%2Fs!AnFi6uGE1rekiUPZG4iE1vFh6Gza%3Fe%3DVu3gES%3C%2FA%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@rangelsammon 

Try this:

 

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

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

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