| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula to sum for different date ranges
I need a formula to total for quarterly date ranges. For example: if a
payment was received during 4/1/08 - 6/30/08 sum the payments. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum for different date ranges
Use cells to hold the date boundries.
Date in A1:A10 Values to sum in B1:B10 D1 = lower date boundary = 4/1/2008 E1 = upper date boundary = 6/30/2008 Then: =SUMIF(A1:A10,"="&D1,B1:B10)-SUMIF(A1:A10,""&E1,B1:B10) Format as General or Number -- Biff Microsoft Excel MVP "confused in tn" <confused in wrote in message ... I need a formula to total for quarterly date ranges. For example: if a payment was received during 4/1/08 - 6/30/08 sum the payments. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum for different date ranges
On Dec 1, 2:27 pm, confused in tn <confused in
wrote: I need a formula to total for quarterly date ranges. For example: if a payment was received during 4/1/08 - 6/30/08 sum the payments. One way, assuming payment dates are in A2:A1000 and payment amounts are in B2:B1000... =SUMPRODUCT(--($A$2:$A$1000=DATE(2008,4,1)),--($A$2:$A$1000<=DATE (2008,6,30)),$B$2:$B$1000) Ken Johnson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum for different date ranges
Can I use the entire columns instead of a range of cells for the dates and
the payments? "Ken Johnson" wrote: On Dec 1, 2:27 pm, confused in tn <confused in wrote: I need a formula to total for quarterly date ranges. For example: if a payment was received during 4/1/08 - 6/30/08 sum the payments. One way, assuming payment dates are in A2:A1000 and payment amounts are in B2:B1000... =SUMPRODUCT(--($A$2:$A$1000=DATE(2008,4,1)),--($A$2:$A$1000<=DATE (2008,6,30)),$B$2:$B$1000) Ken Johnson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula to sum for different date ranges
If you're using Excel 2007 you can. If you're not using Excel 2007 you can
use entire columns with the SUMIF version but not the SUMPRODUCT version. -- Biff Microsoft Excel MVP "confused in tn" wrote in message ... Can I use the entire columns instead of a range of cells for the dates and the payments? "Ken Johnson" wrote: On Dec 1, 2:27 pm, confused in tn <confused in wrote: I need a formula to total for quarterly date ranges. For example: if a payment was received during 4/1/08 - 6/30/08 sum the payments. One way, assuming payment dates are in A2:A1000 and payment amounts are in B2:B1000... =SUMPRODUCT(--($A$2:$A$1000=DATE(2008,4,1)),--($A$2:$A$1000<=DATE (2008,6,30)),$B$2:$B$1000) Ken Johnson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
| Number of days in overlapping date ranges (using array formula?) | Excel Worksheet Functions | |||
| compare date to various date ranges and sum value | Excel Worksheet Functions | |||
| Date ranges in a sum formula | Excel Worksheet Functions | |||
| sum of date ranges | Excel Worksheet Functions | |||
Linear Mode
