SUMPRODUCT Question

Hi,
I have a SUMPRODUCT with multiple criteria and wish to add another
condition using OR ,,,,,, e.g.
A B C D
Pens 18/10/2005 ABC 25
Pens 18/10/2005 BCD 10
Pens 18/10/2005 DEF 15

What I want to achieve is to sum the product in col A that matches the date
in col B
and has a location ( col C ) of either ABC OR BCD and sum col D.

What do you mean by a sum of non-numeric data?

=SUMPRODUCT((B1:B100=DATEVALUE("18-Aug-2005")*((C1:C100="ABC")+(C1:C100="BCD")))
would count the number of rows that match the conditions.

=SUMPRODUCT((B1:B100=DATEVALUE("18-Aug-2005")*((C1:C100="ABC")+(C1:C100="BCD")),D1:D100)
would sum the corresponding values in column D.

Note that multiplication corresponds to a logical "AND" and addition
corresponds to a logical "OR"

Jerry

John Moore wrote:

> Hi,
> I have a SUMPRODUCT with multiple criteria and wish to add another
> condition using OR ,,,,,, e.g.
> A B C D
> Pens 18/10/2005 ABC 25
> Pens 18/10/2005 BCD 10
> Pens 18/10/2005 DEF 15
>
> What I want to achieve is to sum the product in col A that matches the date
> in col B
> and has a location ( col C ) of either ABC OR BCD and sum col D.

=SUMPRODUCT(--(B1:B20=--"2005-10-18"),--ISNUMBER(MATCH(C1:C20,{"ABC","BCD"},
0)),D1:D20)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John Moore" <JohnMoore@discussions.microsoft.com> wrote in message
news:F6486226-59B0-4025-8854-F51892908981@microsoft.com...
> Hi,
> I have a SUMPRODUCT with multiple criteria and wish to add another
> condition using OR ,,,,,, e.g.
> A B C D
> Pens 18/10/2005 ABC 25
> Pens 18/10/2005 BCD 10
> Pens 18/10/2005 DEF 15
>
> What I want to achieve is to sum the product in col A that matches the
date
> in col B
> and has a location ( col C ) of either ABC OR BCD and sum col D.