Alternative to COUNTIFS with different sized ranges?

slam

Well-known Member
Joined
Sep 16, 2002
Messages
792
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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,070
Office Version
  1. 2016
Platform
  1. Windows
If only one appereance in one of the three columns is enough then use 3x COUNTIFS
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,670
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
Excel Formula:
=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

Well-known Member
Joined
Sep 16, 2002
Messages
792
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,670
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I supposed you want to find second criteria at total columns not specific row based your first formula.
Excel 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

Well-known Member
Joined
Sep 16, 2002
Messages
792
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

Well-known Member
Joined
Oct 22, 2012
Messages
1,670
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Glad we can help & Thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,959
Messages
5,627,861
Members
416,278
Latest member
CellHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top