Rolling Year in Excel
I have a similiar situation, however, I would also like a rolling 6 months
and 18 months, any idea how you would do that?
"Frank Kabel" wrote:
Hi
first the SUMIF formula syntax:
=SUMIF(range_to_check,condition,range_to_sum)
So of course the first range is A1:A100 (your dates) and the last range
is column B 8your values. Now the condition part:
"=" & DATE(YEAR(TODAY())-1,MONTH(TODAY()),1)
The DATE formula part uses the current day and creates a date that is
equivalent to the first day of the month 12 months ago. e.g.
TODAY()=13-Jul-2004
This Date formula create the date
1-Jul-2003
Now this is just combined with "=" to get all values which have a date
greater or equal than this calculated date
--
Regards
Frank Kabel
Frankfurt, Germany
Jay S. wrote:
Thank you so much for a quick response!!! Solved my problem exactly,
I am wondering if you might be abel to tell me what the formula does
exactly, I know it does it but what are the specific parts of the
formula doing?
Thanks,
Jay
"Frank Kabel" wrote:
Hi Jay
if column A really consists of date values which are just formated
as
MMMM-YY (but are entered like MM-01-YYYY) then try the following
formula
=SUMIF(A1:A100,"=" &
DATE(YEAR(TODAY())-1,MONTH(TODAY()),1),B1:B100)
--
Regards
Frank Kabel
Frankfurt, Germany
Jay S. wrote:
I am wondering iif I can get Excel to give me the sum of just the
last twelve months in a tabel that contains multiple years.
Example
he Month/Year Days Used
January-02 2
February-02 15
March-02 0
April-02 0
May-02 0
June-02 5
July-02 6
August-02 4
September-02 0
October-02 0
November-02 0
December-02 0
January-03 0
February-03 0
March-03 0
April-03 4
May-03 5
June-03 2
July-03 0
August-03 20
September-03 10
October-03 3
November-03 5
December-03 0
January-04 0
February-04 0
March-04 0
April-04 10
May-04 0
June-04 0
July-04 0
|