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.

Something like

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"Deeds" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

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))*(Range2)))
>
> --
> HTH
>
> Bob Phillips
>
> (remove xxx from email address if mailing direct)
>
> "Deeds" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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))*(Range2)))
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (remove xxx from email address if mailing direct)
> >
> > "Deeds" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> >
> >
> >