Sumproduct with OR

[andy62]

I'm sure there's a way to simplify this but not seeing it in any of the
previous answers. I have a SUMPRODUCT function where the first factor has a
lot of acceptable conditions, and I want to combine them somehow:

=SUMPRODUCT(--(RangeA="A"),--(RangeB="Baltimore))+SUMPRODUCT(--(RangeA="C"),--(RangeB="Baltimore))+SUMPRODUCT(--(RangeA="G"),--(RangeB="Baltimore)) . . .

The only thing changing is the condition in the first factor. rather than
repeat the whole function and add the results together, ideally I could
recode this mess somehow as "=SUMPRODUCT(--(RangeA=OR("A","C","G", . .
..),--(RangeB="Baltimore")). Any ideas? TIA

 

[Sandy Mann]

In SUMPRODUCT() formulas multiplication acts like an AND() statement and
addition acts like an OR() statement so try:

=SUMPRODUCT(((RangeA="A")+(RangeA="B")+(RangeA="C"),--(RangeB="Baltimore))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Teethless mama]

Try this:

=SUMPRODUCT((RangeA={"A","B","C"})*(RangeB="Baltimore"))

 

[andy62]

I'm almost there but one complication. I realized my cells in RangeA do not
= "A" or "B" or "C", they contain "A" and/or "B" and/or "C". I got past that
with the trusty ISNUMBER(SEARCH("A",RangeA)), but those plus signs between
factors are making me count multiple occurences. My SUMPRODUCT result is
looking like 2,2,1,0,3,2 etc. when, instead, I want it to produce a "1" when
the cell contains at least one match and 0 if no matches. Can this be
tweaked of do I need a whole new approach? TIA

 

[Sandy Mann]

This seems to work:

=SUMPRODUCT(--((ISNUMBER(SEARCH("A",RangeA)))+(ISNUMBER(SEARCH("B",RangeA)))+(ISNUMBER(SEARCH("C",RangeA)))>=1),--(RangeB="Baltimore"))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Bernd P]

Hello,

=SUMPRODUCT(SIGN(orcond1+orcond2+...),--andcond2,...)

Regards,
Bernd

 

[Harlan Grove]

This seems to work:

=SUMPRODUCT(--((ISNUMBER(SEARCH("A",RangeA)))
+(ISNUMBER(SEARCH("B",RangeA)))+(ISNUMBER(SEARCH("C",RangeA)))>=1),
--(RangeB="Baltimore"))

....

It should work, but there are shorter ways to do this.

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(rangeA,"A",""),
"B",""),"C","")<>rangeA),--(RangeB="Baltimore"))

which doesn't scale up well, or

=SUMPRODUCT(--(MMULT(--ISNUMBER(SEARCH({"A","B","C"},rangeA)
/(rangeB="Baltimore")),{1;1;1})>0))

which does.

 

[andy62]

Thnaks, I appreciate the shortcut since, in reality, my search includes 13
items, not just the three I simplified down to in my query. I implemented
Sandy's approach and it worked, but will try yours on the second one I have
to do.