Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
rolling year in excel | Excel Worksheet Functions | |||
VBA for Excel 2000 file is corrupt | Excel Discussion (Misc queries) | |||
Excel Files Acting Weird | Excel Discussion (Misc queries) | |||
how do I set dates every 3 cell for whole year in excel | Excel Discussion (Misc queries) |