Alternative to COUNTIFS with different sized ranges?

[slam]

Hi all,

From what I've been able to find, this function only works when range1 and range 2 are the same size (i.e 1 column each in my case). This works great when my formula is this:

=IF(COUNTIFS('Current Order Numbers'!$B:$B,$A3,'Current Order Numbers'!$H:$H,"*"&BA2&"*")>0,"Y","N")

However, I now need to extend the range2 to look not just in column H, but in 3 columns, so I want to change my formula to:

=IF(COUNTIFS('Current Order Numbers'!$B:$B,$A3,'Current Order Numbers'!$H:$J,"*"&BA2&"*")>0,"Y","N")

Essentially, now looking for the value in BA2 to not just a wildcard search in column H, but also column I and column J.

This breaks the formula. What's my solution?

Thank you!

 

[mart37]

If only one appereance in one of the three columns is enough then use 3x COUNTIFS

 

[maabadi]

Try this:

=IF((COUNTIFS('Current Order Numbers'!$B:$B,$A3))*(COUNTIFS('Current Order Numbers'!$H:$H,"*"&BA2&"*")+COUNTIFS('Current Order Numbers'!$I:$I,"*"&BA2&"*")+COUNTIFS('Current Order Numbers'!$J:$J,"*"&BA2&"*"))>0,"Yes","No")

 

[slam]

Thank you both. mart37, I was starting to add the 3 statements when maabadi posted his.

maabadi, I am now using this formula:

=IF($A3="","",IF((COUNTIFS('Current Order Numbers'!$B:$B,$A3))*(COUNTIFS('Current Order Numbers'!$H:$H,"*"&BA$2&"*")+COUNTIFS('Current Order Numbers'!$I:$I,"*"&BA$2&"*")+COUNTIFS('Current Order Numbers'!$J:$J,"*"&BA$2&"*"))>0,"Y","N"))

However, it is giving some strange results, and I can't understand why. For instance, when A3 has a match in $B:$B on the other worksheet, but when there are no matches to BA2 on its row, it is still displaying a Y.

Is it actually giving a Y when it finds a match in the entire H, I, or J columns, instead of just the respective rows?

Thank you!

 

[maabadi]

I supposed you want to find second criteria at total columns not specific row based your first formula.

 =IF((COUNTIFS('Current Order Numbers'!$B:$B,$A3))*(COUNTIFS('Current Order Numbers'!$H2,"*"&BA2&"*")+COUNTIFS('Current Order Numbers'!$I2,"*"&BA2&"*")+COUNTIFS('Current Order Numbers'!$J2,"*"&BA2&"*"))>0,"Yes","No")

And drag it down.

 

[slam]

Hi maabadi..... I think this is working.... but I am actually confused as to what is happening!

It just so happens that the order/contents of column A matches the order/contents of column B on Current Order Numbers.

Is that they only reason this is working? Because otherwise, I'm confused about how we were from a fixed column range search, H:H, to H2, and incrementing as I drag the formula down, and it still works

 

[maabadi]

Glad we can help & Thanks for feedback.