Sumproduct-multiple criteria for same range "OR"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Deeds - 18 May 2006 15:41 GMT

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.

Reply to this Message

Bob Phillips - 18 May 2006 15:55 GMT

Something like

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

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> I found a solution to my problem....
>
[quoted text clipped - 8 lines]
> the conditions where the cell reference is the actual text condition...it
> does not work. Can anyone help? Thanks in advance.

Reply to this Message

Deeds - 18 May 2006 16:14 GMT

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

> Something like
>
[quoted text clipped - 14 lines]
> > the conditions where the cell reference is the actual text condition...it
> > does not work. Can anyone help? Thanks in advance.

Reply to this Message

Bob Phillips - 18 May 2006 16:43 GMT

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.

Signature

HTH

Bob Phillips

(remove xxx from email address if mailing direct)

> Works....thanks much!
> Followup: why do I use the "ISNUMBER" when the criteria is text?....
[quoted text clipped - 18 lines]
> > > the conditions where the cell reference is the actual text condition...it
> > > does not work. Can anyone help? Thanks in advance.

Reply to this Message