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

[junbuggle]

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

I have the following 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

 

[jasonb75]

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

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

 

[RoryA]

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

 

[jasonb75]

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]

I only said “try” because I wasn’t going to try and deconstruct that formula myself either.

 

[junbuggle]

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

(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