Formula to sum sales over 12 month period not based on calendar year

Would appreciate some expert assistance in solving a problem I am having in writing a formula that will sum data for a 12 month period that is not based on a calendar year. For example may have data starting in June 2011 running to date, but not every month. If I set a start date for Year 1, say April 2012, I need to see Year 0 numbers (anything prior to April 2012), Year 1 numbers (April 2012 to March 2013), Year 2 numbers (April 2013 to March 2014), and so on. Date format is currently mmm-yy.

If this could be performed via a macro which references the database (spreadsheet), then even better. The database will contain multiple columns, of which two will contain the date and corresponding number respectively.

Many thanks in advance

Have uploaded file with explanation (clear I hope) of what I am trying to do. Apologies for not having done so before, but am very new to the forum.

Thank you for your quick reply.

Yes, currently there are discrepancies based on the criteria I am setting for the calculation. Theoretically it is possible for EPS sales to occur after a close date, though only for a few months. I was not sure if it was possible to include these in the Year 0 figure once the close date had been set. The numbers you see at the moment are someones subjective interpretation entered manually. I am trying to automate as much as I can. Sales for Year 1 onwards can only have references PLS, SHS, and TFR.

Lastly the numbers in Analysis sheet should be rounded to thousands. The information in the Data sheet is gathered from an external source, from which the output unfortunately cannot be formatted.

Hope this helps.

Apologies for the delayed reply. Just wanted to let you know that the formulas in your file have worked. Many thanks for your input and help. It is much appreciated !

I have re-opened the thread as I have been given additional data. The file will now have to include a calculation for projects that have no Year 1 Start Date. These projects will have revenue (Type EPS). I have tried modifying your formula for this , but am producing a result of double the figure it should be. For example Project XIV EPS revenue - 88,400.

Thank you for your quick reply. Apologies for the confused explanation.

What I now need to happen is 44,200 appearing in the Pre-Commit column (S) regardless of whether there is a start date in column E. Essentially the formula needs to allow for both possibilities. I tried modifying the formula as follows, but if there is a start date it just doubles the total - =SUMIFS(Amount,StartDate,"<"&$E18,ProjName,$D18,Type,"EPS")/1000+SUMIFS(Amount,ProjName,$D18,Type,"EPS")/1000

I have experimented with IF/AND/OR, but to no avail. Possibly that is the answer, but my expertise does not extend that far.

If you are able to provide a solution I would be very grateful.