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

 

excel
Formulas and Functions

5 Replies

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