Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum a range based on Qtr and Year
Hi, I need to do two things - first, write a formula to sum a range of
dollars based on a range of dates. For example: 1/1/2009 300000 2/1/2009 100000 6/1/2010 150000 4/15/2011 90000 If the range of dates is named EqPurDt and the range of dollars is named CapEqPur, I need to be able to sum the dollars spent in Qtr1-2009, Qtr2-2009, etc., then for entire year 2010, year 2011, year2012, etc. I also have name ranges in the worksheet where the years are defined. PAS = 2009, YR2 = 2010, YR3 = 2011, YR4 = 2012, et. I'd like to use date name ranges in the formulas if possible, since this is a template that is used from year to year. Here's my first unsuccessful effort - I'm getting $0 as a result: =SUMPRODUCT(--(EqPurDt=1/1/2009),--(EqPurDt<=3/31/2009),CapEqPur) Does anyone have any ideas? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum a range based on Qtr and Year
Excel will evaluate the dates you entered as arithmetic expressions. Use the
DATE function: =SUMPRODUCT(--(EqPurDt=DATE(2009,1,1)),--(EqPurDt<=DATE(2009,3,31)),CapEqPur) "Dana M" wrote: Hi, I need to do two things - first, write a formula to sum a range of dollars based on a range of dates. For example: 1/1/2009 300000 2/1/2009 100000 6/1/2010 150000 4/15/2011 90000 If the range of dates is named EqPurDt and the range of dollars is named CapEqPur, I need to be able to sum the dollars spent in Qtr1-2009, Qtr2-2009, etc., then for entire year 2010, year 2011, year2012, etc. I also have name ranges in the worksheet where the years are defined. PAS = 2009, YR2 = 2010, YR3 = 2011, YR4 = 2012, et. I'd like to use date name ranges in the formulas if possible, since this is a template that is used from year to year. Here's my first unsuccessful effort - I'm getting $0 as a result: =SUMPRODUCT(--(EqPurDt=1/1/2009),--(EqPurDt<=3/31/2009),CapEqPur) Does anyone have any ideas? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum a range based on Qtr and Year
That worked! I thank you for your kindness!
"bapeltzer" wrote: Excel will evaluate the dates you entered as arithmetic expressions. Use the DATE function: =SUMPRODUCT(--(EqPurDt=DATE(2009,1,1)),--(EqPurDt<=DATE(2009,3,31)),CapEqPur) "Dana M" wrote: Hi, I need to do two things - first, write a formula to sum a range of dollars based on a range of dates. For example: 1/1/2009 300000 2/1/2009 100000 6/1/2010 150000 4/15/2011 90000 If the range of dates is named EqPurDt and the range of dollars is named CapEqPur, I need to be able to sum the dollars spent in Qtr1-2009, Qtr2-2009, etc., then for entire year 2010, year 2011, year2012, etc. I also have name ranges in the worksheet where the years are defined. PAS = 2009, YR2 = 2010, YR3 = 2011, YR4 = 2012, et. I'd like to use date name ranges in the formulas if possible, since this is a template that is used from year to year. Here's my first unsuccessful effort - I'm getting $0 as a result: =SUMPRODUCT(--(EqPurDt=1/1/2009),--(EqPurDt<=3/31/2009),CapEqPur) Does anyone have any ideas? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate Amount Based on Year | Excel Worksheet Functions | |||
Calc Prorated Cost based on Specific Days in a Given Month & Year Range (Revisited..) | Excel Discussion (Misc queries) | |||
How do I change date based on year | Excel Worksheet Functions | |||
Get Data based on Month and Year | Excel Worksheet Functions | |||
Determine every wednesday based off year | Excel Discussion (Misc queries) |