# Alternative to COUNTIFS with different sized ranges?

#### slam

##### Well-known Member
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!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### mart37

##### Well-known Member
If only one appereance in one of the three columns is enough then use 3x COUNTIFS

##### Well-known Member
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
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!

##### Well-known Member

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

##### Well-known Member
Glad we can help & Thanks for feedback.

Replies
2
Views
149
Replies
0
Views
49
Replies
5
Views
331
Replies
3
Views
94
Replies
0
Views
92

1,127,642
Messages
5,626,049
Members
416,158
Latest member

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

### Which adblocker are you using?

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

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