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?

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?

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?