Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum select cells based on date range
Here's the table I have
Date # Days Open 02/01/2008 3 02/01/2008 2 02/05/2008 3 02/11/2008 1 02/11/2008 1 02/12/2008 14 02/15/2008 8 02/18/2008 2 02/18/2008 4 02/20/2008 4 02/21/2008 15 02/22/2008 1 02/26/2008 4 02/27/2008 9 02/27/2008 8 02/29/2008 1 02/29/2008 13 Date is manually input, # days open is calced using networkdays and another manually input date. Out of this table, I can count how many items fall within a date range based using sumproduct. What I need to be able to do is sum the days that fallwithin a date range. For example, the first date range is 02/01/2008 to 02/10/2008. I know there are 3 items there, but I want to add up the 3, 2, and 3 for that range. I have cell references indicating the date range I wish to use. What would I add on to this formula to sumup the # days open for the given date range? I'm using the formula =SUMPRODUCT((J9:J26=AM102)*(J9:J26<=AM103)) to determine the # of items in the date range (date ranges in col AM). I should come up with 8 for the date range of 02/01/2008 to 02/10/2008. Thanks in advance for any help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum select cells based on date range
I prefer my SUMPRODUCTs this way.
=SUMPRODUCT(--(J9:J26=AM102),--(J9:J26<=AM103),K9:K26) HTH, Paul -- "Chad" wrote in message ... Here's the table I have Date # Days Open 02/01/2008 3 02/01/2008 2 02/05/2008 3 02/11/2008 1 02/11/2008 1 02/12/2008 14 02/15/2008 8 02/18/2008 2 02/18/2008 4 02/20/2008 4 02/21/2008 15 02/22/2008 1 02/26/2008 4 02/27/2008 9 02/27/2008 8 02/29/2008 1 02/29/2008 13 Date is manually input, # days open is calced using networkdays and another manually input date. Out of this table, I can count how many items fall within a date range based using sumproduct. What I need to be able to do is sum the days that fallwithin a date range. For example, the first date range is 02/01/2008 to 02/10/2008. I know there are 3 items there, but I want to add up the 3, 2, and 3 for that range. I have cell references indicating the date range I wish to use. What would I add on to this formula to sumup the # days open for the given date range? I'm using the formula =SUMPRODUCT((J9:J26=AM102)*(J9:J26<=AM103)) to determine the # of items in the date range (date ranges in col AM). I should come up with 8 for the date range of 02/01/2008 to 02/10/2008. Thanks in advance for any help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum select cells based on date range
Thanks! worked like a charm!
"PCLIVE" wrote: I prefer my SUMPRODUCTs this way. =SUMPRODUCT(--(J9:J26=AM102),--(J9:J26<=AM103),K9:K26) HTH, Paul -- "Chad" wrote in message ... Here's the table I have Date # Days Open 02/01/2008 3 02/01/2008 2 02/05/2008 3 02/11/2008 1 02/11/2008 1 02/12/2008 14 02/15/2008 8 02/18/2008 2 02/18/2008 4 02/20/2008 4 02/21/2008 15 02/22/2008 1 02/26/2008 4 02/27/2008 9 02/27/2008 8 02/29/2008 1 02/29/2008 13 Date is manually input, # days open is calced using networkdays and another manually input date. Out of this table, I can count how many items fall within a date range based using sumproduct. What I need to be able to do is sum the days that fallwithin a date range. For example, the first date range is 02/01/2008 to 02/10/2008. I know there are 3 items there, but I want to add up the 3, 2, and 3 for that range. I have cell references indicating the date range I wish to use. What would I add on to this formula to sumup the # days open for the given date range? I'm using the formula =SUMPRODUCT((J9:J26=AM102)*(J9:J26<=AM103)) to determine the # of items in the date range (date ranges in col AM). I should come up with 8 for the date range of 02/01/2008 to 02/10/2008. Thanks in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display a range of cells editible cells based on matching date | Excel Worksheet Functions | |||
Auto Sum a Range of Cells based on the date | Excel Discussion (Misc queries) | |||
how can I select a range of cells based on a value of a cell? | Excel Discussion (Misc queries) | |||
Select cell from range based on input in excel xp | Excel Discussion (Misc queries) | |||
Count cells based on date range in another column | New Users to Excel |