| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count within a date range
I have dates in column A and a calculated # in column B. What I need to
determine is for a specified date range from col A, how many in col b are less than or equal to 1. For example: Date range = 02/01/2008 to 02/10/2008 should return a value of 2. I can't figure out the formula to use. I've tried sumif and sumproduct with a count and countif addition (for the <=1), but I can't get the right result. Any one have ideas? Col A Col B 01/30/2008 0 01/31/2008 0 02/01/2008 0 02/05/2008 1 02/11/2008 0 01/24/2008 1 02/06/2008 2 02/15/2008 1 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count within a date range
Put the start date in C1, and the end date in C2, and try this:
=SUMPRODUCT((A1:A8=C1)*(A1:A8<=C2)*(B1:B8<=1)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Chad" wrote in message ... I have dates in column A and a calculated # in column B. What I need to determine is for a specified date range from col A, how many in col b are less than or equal to 1. For example: Date range = 02/01/2008 to 02/10/2008 should return a value of 2. I can't figure out the formula to use. I've tried sumif and sumproduct with a count and countif addition (for the <=1), but I can't get the right result. Any one have ideas? Col A Col B 01/30/2008 0 01/31/2008 0 02/01/2008 0 02/05/2008 1 02/11/2008 0 01/24/2008 1 02/06/2008 2 02/15/2008 1 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count within a date range
Worked perfectly. Sorry for the long delay in replying.
"RagDyeR" wrote: Put the start date in C1, and the end date in C2, and try this: =SUMPRODUCT((A1:A8=C1)*(A1:A8<=C2)*(B1:B8<=1)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Chad" wrote in message ... I have dates in column A and a calculated # in column B. What I need to determine is for a specified date range from col A, how many in col b are less than or equal to 1. For example: Date range = 02/01/2008 to 02/10/2008 should return a value of 2. I can't figure out the formula to use. I've tried sumif and sumproduct with a count and countif addition (for the <=1), but I can't get the right result. Any one have ideas? Col A Col B 01/30/2008 0 01/31/2008 0 02/01/2008 0 02/05/2008 1 02/11/2008 0 01/24/2008 1 02/06/2008 2 02/15/2008 1 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Count within a date range
Appreciate the feed-back, no matter how belated it might be!<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Chad" wrote in message ... Worked perfectly. Sorry for the long delay in replying. "RagDyeR" wrote: Put the start date in C1, and the end date in C2, and try this: =SUMPRODUCT((A1:A8=C1)*(A1:A8<=C2)*(B1:B8<=1)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Chad" wrote in message ... I have dates in column A and a calculated # in column B. What I need to determine is for a specified date range from col A, how many in col b are less than or equal to 1. For example: Date range = 02/01/2008 to 02/10/2008 should return a value of 2. I can't figure out the formula to use. I've tried sumif and sumproduct with a count and countif addition (for the <=1), but I can't get the right result. Any one have ideas? Col A Col B 01/30/2008 0 01/31/2008 0 02/01/2008 0 02/05/2008 1 02/11/2008 0 01/24/2008 1 02/06/2008 2 02/15/2008 1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I count a record if it contains a date within a date range | Excel Worksheet Functions | |||
| Count cells within a date range | Excel Discussion (Misc queries) | |||
| count records in a date range | Excel Discussion (Misc queries) | |||
| How to count dates within a certain range in a column with mutiple date range entries | Excel Worksheet Functions | |||
| count weekdays in a date range | Excel Worksheet Functions | |||
Linear Mode
