Countifs 3 conditions head scratcher.

[Oldsquid-1998]

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.

[#2]

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.

[#3]

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.

[#4]

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

[#5]

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 

[#6]

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