Automatically update formula every month

Olga N Barabash

Hi,

I have a sheet listing expenses for every month this year, and expenses for every month last year (Jan 2015, Jan 2016, Feb 2015, Feb 2016 etc).

 

There is also a column for Total YTD 2015, Total YTD 2016 (if today is December, it would add Jan-Nov 2015 in one column and Jan-Nov 2016 in a column next to it; for comparison).

 

There are always figures in all 2015 monthly columsn (jan-Dec2015) since we know what the expense were for all last year.

The YTD for 2016 is easy, as the month's we don't have expenses for yet (current or future months) so those will not affect the total.

However, I need the YTD 2015 Total to not capture the current or future months, without having to update this formula every month. This way i can compare apples to apples, Jan-Nov 2015 through Jan-Nov 2016.

 

How can I do this? Some kind of an If or index formular perhaps?

 

 

Thanks!

Christian Wells

Hi Olga,

 

I thought of Sum ifs straight away when I read your Query.

 

If you identify the columns by year and month separately

Then you can calculate;

the month of reporting limit (Current month -1)

The Sum of the Values in the Row where the Identified year = Somthing (2015) and the months are Less than the reporting limit month.

 

 

 

The boxed cell formula is

=SUMIFS([Jan 2015]1:[Dec 2016]1, [Jan 2015]4:[Dec 2016]4, "2015", [Jan 2015]5:[Dec 2016]5, <=[YTD 2016]6)

 

Translated = sum row 1 where row 4 = 2015 and row 5 <=11.

 

Hope this helps