SUMPRODUCT Question

[Guest]

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.

 

[Jerry W. Lewis]

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")),D1100)
would sum the corresponding values in column D.

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

Jerry

 

[Bob Phillips]

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

--

HTH

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