using a range of dates

[Guest]

I have an application where I have one column filled with dates, say column
A, I have another column filled with numbers representing a product, say
column B. I am trying to count the number times a particular value in column
B is present between a range of dates.
Example: the number of times 4 is present in column B between 12/1/2004 and
12/31/2004.

HELP!!!

 

[Trevor Shuttleworth]

MinnesotaBob

one way:

=SUMPRODUCT(--(A2:A68>=DATEVALUE("1/12/2004"))*--(A2:A68<=DATEVALUE("31/12/2004"))*--(B2:B68=4))

Or, if you have the dates as *text* in cells J2 and J3 (for example)

=SUMPRODUCT(--(A2:A68>=DATEVALUE(J2))*--(A2:A68<=DATEVALUE(J3))*--(B2:B68=4))

Or, with true dates in cells J7 and J8 and the value you are looking for in
J9:

=SUMPRODUCT(--(A2:A68>=DATE(YEAR(J7),MONTH(J7),DAY(J7)))*--(A2:A68<=DATE(YEAR(J8),MONTH(J8),DAY(J8)))*--(B2:B68=J9))

You'll need to change the dates round ... I'm in the UK, hence 31/12/2004,
etc.

Regards

Trevor

 

[Guest]

Thanks Trevor I'll get one of them to work. I appriciate your time and help.