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