Sumproduct-multiple criteria for same range "OR"
 

I found a solution to my problem....

However, to designate "or" between multiple conditions existing in a
*single* range, you can create an array of these "or" conditions, using an
array constant, as:
=Sumproduct((Range1-{Condition1,Condition2,Condition3})*( ... )).

The above solution works IF I type in the conditions which are text...so
when I type in "Condition1" in quotes and "Condition2" in quotes it works as
I want it as an OR statement. However, when I try to put a cell reference as
the conditions where the cell reference is the actual text condition...it
does not work. Can anyone help? Thanks in advance.

Sumproduct-multiple criteria for same range "OR"
 

Something like

=SUMPRODUCT((ISNUMBER(MATCH(Range1,E1:E2,0))*(Rang e2)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" wrote in message
...
I found a solution to my problem....

However, to designate "or" between multiple conditions existing in a
*single* range, you can create an array of these "or" conditions, using an
array constant, as:
=Sumproduct((Range1-{Condition1,Condition2,Condition3})*( ... )).

The above solution works IF I type in the conditions which are text...so
when I type in "Condition1" in quotes and "Condition2" in quotes it works
as
I want it as an OR statement. However, when I try to put a cell reference
as
the conditions where the cell reference is the actual text condition...it
does not work. Can anyone help? Thanks in advance.

Sumproduct-multiple criteria for same range "OR"
 

Works....thanks much!
Followup: why do I use the "ISNUMBER" when the criteria is text?....
thanks for your help.

"Bob Phillips" wrote:

Something like

=SUMPRODUCT((ISNUMBER(MATCH(Range1,E1:E2,0))*(Rang e2)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" wrote in message
...
I found a solution to my problem....

However, to designate "or" between multiple conditions existing in a
*single* range, you can create an array of these "or" conditions, using an
array constant, as:
=Sumproduct((Range1-{Condition1,Condition2,Condition3})*( ... )).

The above solution works IF I type in the conditions which are text...so
when I type in "Condition1" in quotes and "Condition2" in quotes it works
as
I want it as an OR statement. However, when I try to put a cell reference
as
the conditions where the cell reference is the actual text condition...it
does not work. Can anyone help? Thanks in advance.

Sumproduct-multiple criteria for same range "OR"
 

The ISNUMBER does not refer to the value being tested, but the result of the
MATCYH function. If the Match finds a MATCH, it returns a numeric index,
else it returns an error. ISNUMBER measures that.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" wrote in message
...
Works....thanks much!
Followup: why do I use the "ISNUMBER" when the criteria is text?....
thanks for your help.

"Bob Phillips" wrote:

Something like

=SUMPRODUCT((ISNUMBER(MATCH(Range1,E1:E2,0))*(Rang e2)))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" wrote in message
...
I found a solution to my problem....

However, to designate "or" between multiple conditions existing in a
*single* range, you can create an array of these "or" conditions,
using an
array constant, as:
=Sumproduct((Range1-{Condition1,Condition2,Condition3})*( ... )).

The above solution works IF I type in the conditions which are
text...so
when I type in "Condition1" in quotes and "Condition2" in quotes it
works
as
I want it as an OR statement. However, when I try to put a cell
reference
as
the conditions where the cell reference is the actual text
condition...it
does not work. Can anyone help? Thanks in advance.