Sumproduct with Date column
 

I have multiple columns where one contains a date formated as 6/6/2005 for
example. I have other columns with the rep id and product numbers. I want to
show how many of a product did a rep sell on a particular date. The problem
I'm having is getting the formula to work with the date. The data looks
something like this. Any sugegstions?

A B C
JS 253 6/6/2005
BO 134 6/3/2005
BO 253 6/6/2005


--
JerryS

Assuming the sample data is in A1:C3,
you could use in say, D1:

=SUMPRODUCT((A1:A3="BO")*(C1:C3=DATE(2005,6,3)),B1 :B3)

which returns the number 134
(for the rep "BO" on 3 Jun 2005)

Note that you can't use entire col refs in SUMPRODUCT
and that the ranges: A1:A3, C1;C3 etc should be identical in structure
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"JerryS" wrote in message
...
I have multiple columns where one contains a date formated as 6/6/2005 for
example. I have other columns with the rep id and product numbers. I want
to
show how many of a product did a rep sell on a particular date. The
problem
I'm having is getting the formula to work with the date. The data looks
something like this. Any sugegstions?

A B C
JS 253 6/6/2005
BO 134 6/3/2005
BO 253 6/6/2005


--
JerryS

just put a date formatted the same into a cell somewhere (a1)
=sumproduct((c2:c2000=a1)*1)
to count the sales on that date

--
Don Guillett
SalesAid Software

"JerryS" wrote in message
...
I have multiple columns where one contains a date formated as 6/6/2005 for
example. I have other columns with the rep id and product numbers. I want
to
show how many of a product did a rep sell on a particular date. The
problem
I'm having is getting the formula to work with the date. The data looks
something like this. Any sugegstions?

A B C
JS 253 6/6/2005
BO 134 6/3/2005
BO 253 6/6/2005


--
JerryS