Need help determining what formula to use

%3CLINGO-SUB%20id%3D%22lingo-sub-864156%22%20slang%3D%22en-US%22%3ENeed%20help%20determining%20what%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-864156%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20just%20learning%20excel%20and%20am%20not%20sure%20how%20to%20do%20the%20below%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20trying%20to%20count%20the%20number%20of%20times%20a%20%22pass%20successful%22%20comes%20up%20directly%20after%20a%20pass%20is%20thrown.%20Eg.%20For%20every%20%E2%80%9CGA%20Chest%20Pass%E2%80%9D%20how%20many%20times%20is%20this%20followed%20by%20%26nbsp%3B%E2%80%9CPass%20success%E2%80%9D%20in%20the%20next%20cell%20along.%3C%2FP%3E%3CP%3EDoes%20that%20make%20sense%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-864156%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866542%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20determining%20what%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866542%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412130%22%20target%3D%22_blank%22%3E%40BEliza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20my%20understanding%20from%20your%20post%2C%20you%20can%20use%20below%26nbsp%3B%3CSTRONG%3ECountifs()%3C%2FSTRONG%3E%20function%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22book%20antiqua%2Cpalatino%22%3E%3CSTRONG%3E%3DCOUNTIFS(%24C%244%3A%24C%2413%2C%22%3D%22%26amp%3BF4%2C%24D%244%3A%24D%2413%2C%22pass%20successful%22)%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20sample%20file%20is%20also%20attached%20for%20your%20reference.%20You%20may%20also%20share%20your%20actual%20data%20so%20that%20more%20specific%20solution%20can%20be%20suggested.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-866947%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20determining%20what%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-866947%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239215%22%20target%3D%22_blank%22%3E%40tauqeeracma%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that%2C%20its%20almost%20right%20I%20think.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20picture%20below%20of%20a%20fake%20data%20set%20shows%20that%20I%20have%20different%20pass%20styles%20that%20have%20%22pass%20success%22%20after%20it.%20The%20formula%20you%20posted%20counted%20all%20of%20the%20%22Pass%20success%22%20not%20just%20the%20ones%20after%20%22GA%20Chest%20pass%22.%20How%20do%20I%20refine%20it%20to%20show%20how%20successful%20each%20individual%20pass%20type%20is%20separately%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F132756i54A161B45B773515%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Excel.png%22%20title%3D%22Excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-867153%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20determining%20what%20formula%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-867153%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F412130%22%20target%3D%22_blank%22%3E%40BEliza%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20use%20below%20modified%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%20face%3D%22helvetica%22%3E%3DCOUNTIFS(%24C%244%3A%24C%2413%2C%22GA%20Chest%22%2C%24D%244%3A%24D%2413%2C%22Pass%20Successful%22)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20it%20will%20count%20all%20%22GA%20Checst%22%20only%20if%20they%20are%20followed%20by%20%22Pass%20Successful%22%3C%2FP%3E%3CP%3EHope%20it%20will%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETauqeer%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, I am just learning excel and am not sure how to do the below

 

I’m trying to count the number of times a "pass successful" comes up directly after a pass is thrown. Eg. For every “GA Chest Pass” how many times is this followed by  “Pass success” in the next cell along.

Does that make sense?

 

Thank you!

3 Replies
Highlighted

Hi @BEliza 

 

Based on my understanding from your post, you can use below Countifs() function:

 

=COUNTIFS($C$4:$C$13,"="&F4,$D$4:$D$13,"pass successful")

 

A sample file is also attached for your reference. You may also share your actual data so that more specific solution can be suggested.

 

Thanks

Tauqeer

Highlighted

@tauqeeracma 

 

Thanks for that, its almost right I think. 

 

The picture below of a fake data set shows that I have different pass styles that have "pass success" after it. The formula you posted counted all of the "Pass success" not just the ones after "GA Chest pass". How do I refine it to show how successful each individual pass type is separately?

 

Excel.png

 

Highlighted

@BEliza 

Please use below modified formula:

 

=COUNTIFS($C$4:$C$13,"GA Chest",$D$4:$D$13,"Pass Successful")

 

Now it will count all "GA Checst" only if they are followed by "Pass Successful"

Hope it will help.

 

Tauqeer