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

junbuggle

New Member
Joined
Mar 9, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,618
Office Version
  1. 365
Platform
  1. Windows
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:
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could try transposing the second array by using {"bB";"ULv";"FREE"}
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,618
Office Version
  1. 365
Platform
  1. Windows
Or that :oops:

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
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I only said “try” because I wasn’t going to try and deconstruct that formula myself either. ;)
 

junbuggle

New Member
Joined
Mar 9, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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 :oops:

@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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,517
Messages
5,636,804
Members
416,941
Latest member
shazzaxyz

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