Home

Countifs 3 conditions head scratcher.

%3CLINGO-SUB%20id%3D%22lingo-sub-309288%22%20slang%3D%22en-US%22%3ECountifs%203%20conditions%20head%20scratcher.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309288%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20not%20new%20to%20Excel%2C%20but%20I%20am%20new%20to%20combining%20formulas.%20I%20can%E2%80%99t%20seem%20to%20grasp%20if%20this%20is%20possible%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20differentiate%20the%20two%20types%20of%20late%20orders%2C%20Adj%20and%20UnAdj.%3C%2FP%3E%3CP%3EIn%20column%20B%20I%20have%20%3DCOUNTIF(%24J%244%3A%24J%246%2C%22*%22%26amp%3BD4%26amp%3B%22*%22)%20to%20count%20the%20number%20of%20lates%20reported%20in%20column%20J%2C%20this%20is%20working.%3C%2FP%3E%3CP%3EIn%20column%20F%20%26amp%3B%20H%20are%20the%20codes%20for%20any%20lates.%20Column%20J%20is%20system%20generated%2C%20column%20K%20is%20the%20codes%20for%20the%20late%20(this%20varies%20per%20order).%3C%2FP%3E%3CP%3EIn%20M4%20I%20have%20%3DIF(B4%3D1%2C(COUNTIF(Adj%2CK4)))%2C%20and%20in%20O4%20%3DIF(B4%3D1%2C(COUNTIF(UnAdj%2CK4))).%20What%20I%20not%20understanding%20is%20how%20to%20get%20it%20to%20work%20when%20copied%20down%20to%20M5%20%2F%20O5%20%26amp%3B%20M6%20%2F%20O6.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309288%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-375759%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%203%20conditions%20head%20scratcher.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375759%22%20slang%3D%22en-US%22%3E%3CP%3EWell%2C%20I%20spoke%20too%20soon%2C%20there%20is%20still%20one%20thing%20that%20it's%20not%20doing%2C%20that%20is%20giving%20a%20total%20count%20of%20occurrences.%26nbsp%3B%20See%20worksheet%2020.%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-375662%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%203%20conditions%20head%20scratcher.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-375662%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20reason%20for%20the%20Adj%20%26amp%3B%20UnAdj%20is%20Corp%20wants%20to%20know%20if%20the%20last%20is%20something%20the%20warehouse%20can%20control%20(late%20cutting)%20or%20could%20not%20control%20(late%20incoming%20material).%3C%2FP%3E%3CP%3EThis%20is%20exactly%20what%20I've%20been%20trying%20to%20wrap%20my%20head%20around.%26nbsp%3B%20I%20have%20never%20tried%20to%20learn%20Index%20%26amp%3B%20Match%2C%20have%20watched%20YouTube%20on%20it%2C%20but%20couldn't%20grasp%20the%20concept.%26nbsp%3B%20Thank%20you%20very%20much.%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355760%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%203%20conditions%20head%20scratcher.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355760%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20clarify%20a%20logic%20behind.%20Transport%20in%20D5%20is%20not%20late%2C%20thus%20there%20is%20no%20Late%20reason%20for%20it%20in%20Adj%20and%20we%20return%20zero.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20previous%20logic%20I%20adjusted%20formula%20a%20bit%20-%20for%20Transport%20we%20find%20Late%20code%2C%20if%20such%20doesn't%20exist%20(not%20late)%20or%20it's%20not%20in%20Adj%20we%20return%20zero%2C%20otherwise%201.%20Same%20for%20UnAdj%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIF(Adj%2CINDEX(Late%2CMATCH(%22*%22%26amp%3BD4%26amp%3B%22*%22%2C%24J%244%3A%24J%246%2C0)))%3C%2FPRE%3E%0A%3CP%3EIn%20attached%20file%20records%20with%20found%20Late%20code%20are%20in%20yellow%20(conditional%20formatting%20is%20applied).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-355752%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%203%20conditions%20head%20scratcher.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-355752%22%20slang%3D%22en-US%22%3E%3CP%3ESorry%20for%20the%20long%20delay%20in%20answering%2C%20life%20has%20gotten%20in%20the%20way%20of%20learning.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20it%20is%20not%20counting%20anything%20in%20the%20%22Adj.%22%20column%2C%20K5%20should%20have%20counted%20as%201%20in%20M5.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309293%22%20slang%3D%22en-US%22%3ERe%3A%20Countifs%203%20conditions%20head%20scratcher.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309293%22%20slang%3D%22en-US%22%3E%3CP%3EThey%20works%20if%20you%20drag%20them%20down.%20And%20if%20add%20missed%20optional%20second%20parameter%20for%20IF%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(B4%3D1%2C(COUNTIF(Adj%2CK4))%2C0)%3C%2FPRE%3E%0A%3CP%3Ethey%20will%20return%20zero%20instead%20of%20FALSE%2C%20but%20that's%20only%20formatting%20issue.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Oldsquid-1998
Occasional Contributor

I am not new to Excel, but I am new to combining formulas. I can’t seem to grasp if this is possible or not.

 

I am trying to differentiate the two types of late orders, Adj and UnAdj.

In column B I have =COUNTIF($J$4:$J$6,"*"&D4&"*") to count the number of lates reported in column J, this is working.

In column F & H are the codes for any lates. Column J is system generated, column K is the codes for the late (this varies per order).

In M4 I have =IF(B4=1,(COUNTIF(Adj,K4))), and in O4 =IF(B4=1,(COUNTIF(UnAdj,K4))). What I not understanding is how to get it to work when copied down to M5 / O5 & M6 / O6.

 

5 Replies

They works if you drag them down. And if add missed optional second parameter for IF like

=IF(B4=1,(COUNTIF(Adj,K4)),0)

they will return zero instead of FALSE, but that's only formatting issue.

Sorry for the long delay in answering, life has gotten in the way of learning.

 

But it is not counting anything in the "Adj." column, K5 should have counted as 1 in M5.

Hi,

 

Could you please clarify a logic behind. Transport in D5 is not late, thus there is no Late reason for it in Adj and we return zero.

 

For the previous logic I adjusted formula a bit - for Transport we find Late code, if such doesn't exist (not late) or it's not in Adj we return zero, otherwise 1. Same for UnAdj

=COUNTIF(Adj,INDEX(Late,MATCH("*"&D4&"*",$J$4:$J$6,0)))

In attached file records with found Late code are in yellow (conditional formatting is applied).

The reason for the Adj & UnAdj is Corp wants to know if the last is something the warehouse can control (late cutting) or could not control (late incoming material).

This is exactly what I've been trying to wrap my head around.  I have never tried to learn Index & Match, have watched YouTube on it, but couldn't grasp the concept.  Thank you very much.@Sergei Baklan 

Well, I spoke too soon, there is still one thing that it's not doing, that is giving a total count of occurrences.  See worksheet 20. @Sergei Baklan 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies