sum a range based on Qtr and Year

D

Dana M

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?
 
B

bapeltzer

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)
 
D

Dana M

That worked! I thank you for your kindness!

bapeltzer said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top