# #N/A when combining multiple criteria with SumProduct (but individual criteria work!)

#### junbuggle

##### New Member
Hi there, I have a strange problem and I was hoping someone might be able to help me.

I have the following formula:

Excel Formula:
``=SUMPRODUCT(ISNUMBER(SEARCH({"Pmm","Cnn"},INDIRECT(Config!\$B\$6&Config!\$B\$2&":"&Config!\$B\$6&Config!\$B\$1)))*ISNUMBER(SEARCH({"bB","ULv","FREE"},INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),"1","")&Config!\$B\$2&":"&SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),"1","")&Config!\$B\$1)))*SUBTOTAL(9,OFFSET(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!\$B\$2),ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!\$B\$2&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!\$B\$1))-MIN(ROW(INDIRECT(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!\$B\$2&":"&SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")&Config!\$B\$1))),0)))``

It gives me a #N/A error.

What I find strange is that if I take out the first IsNumber function, this formula works. And if I instead take out the second IsNumber function, it also works.

But if I have both in there, it doesn't work. I'm not sure why I can't stack them to have an AND operator functioning... I have other formulas where I'm able to get multiple criteria working by multiplying.

I know I haven't provided underlying data, but I'm hoping that there might be an obvious reason why I'm not able to stack both IsNumber formulas.

And the underlying data and range references are correct, because using the first or the second IsNumber formula alone does provide a value. It's just combining them that's giving me a headache.

Does anyone have any ideas? I would be really grateful!

Thanks

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### jasonb75

##### Well-known Member
The short version, the array constants in the 2 searches are not equal in size. When you set it up this way, the search is "Pmm" and "bB", or "Cnn" and ULv". Because there is no criteria in the first search to pair with "FREE", an error occurs.

If you want to pair the criteria in a matrix so that you have, "Pmm" and "bB", or "Pmm" and ULv", or "Pmm" and "FREE", etc then I think that you might be out of luck with a single formula that can handle all of the criteria that you have specified.

The only way that comes to mind that will work (very long winded) would be
Excel Formula:
``(ISNUMBER(SEARCH("Pmm",range))+ISNUMBER(SEARCH("Cnn",range)))*(ISNUMBER(SEARCH("bB",range))+ISNUMBER(SEARCH("ULv",range))+ISNUMBER(SEARCH("FREE",range)))``
Note that SEARCH is not case sensitive, if you need it to be exactly as entered then you need to use FIND instead.

Last edited:

#### RoryA

##### MrExcel MVP, Moderator
You could try transposing the second array by using {"bB";"ULv";"FREE"}

#### jasonb75

##### Well-known Member
Or that

I did think of that originally, but dismissed it in case the range could be multiple columns. In hindsight, if it was then the individual formulas would have also failed.
In all honesty, when I got as far as INDIRECT(SUBSTITUTE(ADDRESS( I stopped trying to break the formula down properly and assumed worst case scenario.

#### RoryA

##### MrExcel MVP, Moderator
I only said “try” because I wasn’t going to try and deconstruct that formula myself either.

#### junbuggle

##### New Member
You could try transposing the second array by using {"bB";"ULv";"FREE"}

Thanks for the suggestion! Interestingly, this didn't work. It just didn't calculate at all...

The short version, the array constants in the 2 searches are not equal in size. When you set it up this way, the search is "Pmm" and "bB", or "Cnn" and ULv". Because there is no criteria in the first search to pair with "FREE", an error occurs.

If you want to pair the criteria in a matrix so that you have, "Pmm" and "bB", or "Pmm" and ULv", or "Pmm" and "FREE", etc then I think that you might be out of luck with a single formula that can handle all of the criteria that you have specified.

The only way that comes to mind that will work (very long winded) would be
Excel Formula:
``(ISNUMBER(SEARCH("Pmm",range))+ISNUMBER(SEARCH("Cnn",range)))*(ISNUMBER(SEARCH("bB",range))+ISNUMBER(SEARCH("ULv",range))+ISNUMBER(SEARCH("FREE",range)))``
Note that SEARCH is not case sensitive, if you need it to be exactly as entered then you need to use FIND instead.

This worked! Thank you so much! Yes, a bit longer than was originally the case, but this spreadsheet I'm working on is at a stage where I'm just trying to iron out a few last things, and I am desperate to just get it across the line. This works perfectly for that purpose

Also, I realise I should have just used `[Range]` or something when posting my original formula. That would have helped readability. Didn't even occur to me at the time

@RoryA and @jasonb75 , thank you both so much for taking the time to respond. I would never have been able to figure this out on my own, even with reading countless articles. I am really very grateful

Replies
7
Views
801
Replies
0
Views
927
Replies
4
Views
189
Replies
10
Views
146
Replies
4
Views
208

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,954
Messages
5,834,562
Members
430,296
Latest member
Prosen

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