Sum Values Within the Current Financial Year

[Vulpix89]

KPI Report - Actual vs Target1.xlsx

Geniuses assemble!

I have an excel report (run through Sage/Alchemex) showing various figures for the business starting with the most recently closed off month (currently July) and the previous 11 months worth of data. When run, the report automatically rolls forward to the most recently closed month, so when I run it on September 1st it will have rolled forward to show August 2013 and August 2012 will have dropped off, und so weiter.

I need the report to show a Year to Date total that only sums the values that are within the current financial year. Currently, that will just be one column, July, and then next month it will need to pick up August, but ignore all columns from previous financial years which are just included for comparison.

I have fruitlessly toyed with SUM, IF and SUMIF functions to no avail. Can anyone help? A copy of the report is attached.

[FDibbins]

Hi and welcome to the forum

I have fruitlessly toyed with SUM, IF and SUMIF functions to no avail

aahh but have you toyed with sumifS() yet?

[Vulpix89]

Indeed, but I lack a great deal in skill with syntax. I thought I had hit my eureka moment when I finally got a formula to return a value (instead of the error messages that are the precursors to tantrums) but alas, it was a value of 0. My assumption there is that because only one of the cells in my range was equal to or greater than the current financial year, not all of them, it didn't sum any of them.

Though I could be wrong. It has happened before.

[FDibbins]

please note that I suggested sumifS(), not just sumif() (note the extra S)

=sumifS() allows for more than 1 criteria to base the sum off

I will take a look at your file, but take a look at the sumifS() function - AND (looked at your file) instead of using text dates (August September) etc, change them to real dates 8/1/2012, 9/1/2012 (or in aussie...1/8/2012, 1/9/2012). That way, calcs can be done using them as references

[FDibbins]

OK I looked, and a plain ol sumif() will work (IF I understand what you want), but 1st you need to change the "dates" to real dates as I suggested. Then you can use this, copied down...

=SUMIF($E$2:$R$2,">=6/1/2013",$E3:$R3)

If you dont know how the $ works (absoluting) let me know and i will explain.
Also, with many excel functions, if you use an operator (<, >, <=, >=), you need to put it in parentheses, as I did above

[Vulpix89]

Ahh perfect! I was so close before, but I was trying to reference a cell containing the date of the beginning of the financial year instead of including it in the formula itself.

Thank you very much!

[FDibbins]

you'r welcome. If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below)