Forum Discussion

Oldsquid-1998's avatar
Oldsquid-1998
Copper Contributor
Jan 05, 2019

Countifs 3 conditions head scratcher.

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...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 05, 2019

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.

Oldsquid-1998's avatar
Oldsquid-1998
Copper Contributor
Feb 23, 2019

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.

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 23, 2019

    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).

    • Oldsquid-1998's avatar
      Oldsquid-1998
      Copper Contributor
      Mar 16, 2019

      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.SergeiBaklan