sum automatically from last 12 months based on current date
 

I have a database consisting of monthly hours covering the last 10 years.
Occassionally, I need to sum up the hours from the last 12 months. Which
Excel function do I use so that a cell will automatically show the sum of
hours from the last 12 months based on the current date?

assuming valid dates in col A, try

=SUMPRODUCT((A1:A40=EDATE(TODAY(),-12))*(A1:A40<=TODAY())*B1:B40)

--
Don Guillett
SalesAid Software

"CDSchomaker" wrote in message
...
I have a database consisting of monthly hours covering the last 10 years.
Occassionally, I need to sum up the hours from the last 12 months. Which
Excel function do I use so that a cell will automatically show the sum of
hours from the last 12 months based on the current date?

Hi
and just an alternative to Don' solution which does not need the Analysis
Toolpak Addin:
=SUMPRODUCT(--(A1:A100=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),--(A1:A100<=TODAY()),B1:B100)

Also check:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards
Frank Kabel
Frankfurt, Germany

CDSchomaker wrote:
I have a database consisting of monthly hours covering the last 10
years. Occassionally, I need to sum up the hours from the last 12
months. Which Excel function do I use so that a cell will
automatically show the sum of hours from the last 12 months based on
the current date?