Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a range of dates
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
using a range of dates
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 "MinnesotaBob" wrote in message ... 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!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Min and Max Dates in Range that Contains Zeroes, Dates, and Number | Excel Discussion (Misc queries) | |||
Identifying unique dates in a range of cells containing dates... | Excel Discussion (Misc queries) | |||
Identifying unique dates within a range of cells containing dates | Excel Discussion (Misc queries) | |||
How do I find the earliest dates in a range of dates? | Excel Worksheet Functions | |||
How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions |