Help needed with isnumber multiple search array formula

  • Thread starter Thread starter J_Dodds
  • Start date Start date
J

J_Dodds

=SUM(IF(($S$4:$S$600="Y")*
ISNUMBER(SEARCH("ASDA",'$K$4:$K$600)),'$N$4:$N$600))

I have a spreadsheet which currently uses the above formula, which
basically checks if one coloumn contains "asda", another coloumn
contains "Y" and adds u any figures in another column where both of
these instances are found.

However I need to create a similiar formular that will search for
multiple text and not just "asda" eg any of "asda", "ress" "consumer"
"assurance".

Finally I also need to use the multiple search but use a NOT eg
as above but does NOT contain ... eg any of "asda", "ress" "consumer"
"assurance".
Any help would be appreciated :-)
 
Hi

Instead your formula you can use:
=SUMPRODUCT(($S$4:$S$600="Y")*($K$4:$K$600="ASDA")*($N$4:$N$600))
With several values for column K the formula will be:
=SUMPRODUCT(($S$4:$S$600="Y")*($K$4:$K$600={"ASDA";"RESS";"CONSUMER";"ASSURA
NCE"})*($N$4:$N$600))
(I'm not sire about list - in curly brackets - delimiter here, maybe it's
comma)
For case NOT containing and single value:
=SUMPRODUCT(($S$4:$S$600="Y")*($K$4:$K$600<>"ASDA")*($N$4:$N$600))
For case NOT containing and several values the formula
=SUMPRODUCT(($S$4:$S$600="Y")*($K$4:$K$600<>{"ASDA";"RESS";"CONSUMER";"ASSUR
ANCE"})*($N$4:$N$600))
doesn't work. Easiest will be to sum all values having "Y" in $K$4:$K$600
and substract the second formula, i.e.
=SUMPRODUCT(($S$4:$S$600="Y")*($N$4:$N$600))-SUMPRODUCT(($S$4:$S$600="Y")*($
K$4:$K$600={"ASDA";"RESS";"CONSUMER";"ASSURANCE"})*($N$4:$N$600))
 
=SUMPRODUCT(--($S$4:$S$600="Y"),--ISNUMBER(MATCH($K$4:$K$600,{"asda","ress",
"consumer","assurance"},0)),$N$4:$N$600)

will work faster.

You can also have {"asda","ress","consumer","assurance"} in a range, say
X2:X5, and use:

=SUMPRODUCT(--($S$4:$S$600="Y"),--ISNUMBER(MATCH($K$4:$K$600,$X$2:$X$5,0)),$
N$4:$N$600)

Since you're also interested in using the complement of X2:X5, either
enumerate the complement or use:

=SUMPRODUCT(--($S$4:$S$600="Y"),--ISNA(MATCH($K$4:$K$600,$X$2:$X$5,0)),$N$4:
$N$600)
 
Thanks for all your help.

But I'm not sure if the formulas given will work as I think I need the
search as column K contains lots of text and not just single words
e.g. blah blah blah ... asda blah blah blah. Let me know if I am wrong
but I tried the suggested formulas and I was just getting a result of
0.
 
Back
Top