Sumproduct with OR
 

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

Sumproduct with OR
 

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


Replace @mailinator.com with @tiscali.co.uk


"andy62" wrote in message
...
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

Sumproduct with OR
 

Try this:

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


"andy62" wrote:

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

Sumproduct with OR
 

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" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"andy62" wrote in message
...
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

Sumproduct with OR
 

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


Replace @mailinator.com with @tiscali.co.uk


"andy62" wrote in message
...
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" wrote:

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


Replace @mailinator.com with @tiscali.co.uk


"andy62" wrote in message
...
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

Sumproduct with OR
 

Hello,

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

Regards,
Bernd

Sumproduct with OR
 

"Sandy Mann" wrote...
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.

Sumproduct with OR
 

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.

"Harlan Grove" wrote:

"Sandy Mann" wrote...
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.