Sum through n months
To sum a set of monthly data through n number of months, you can use a formula based on the SUM and OFFSET functions. In the example shown, the formula in G6 is:
How this formula works
In the example shown, we have monthly data for the years 2017 and 2018. The goal is to dynamically sum values through a given number of months, hardcoded as 6 in cell G5. This is done by feeding a reference constructed with the OFFSET function into the sum function.
The OFFSET function let's you build a reference using a starting point, a row and column offset, and a height and width. OFFSET is handy in formulas that dynamically average or sum "last n values", "first n values", and so on. In cell G6, OFFSET is configured like this:
Translated: from a starting position of C5, build a reference 6 rows x 1 column with no offset. With the number 6 in G5, OFFSET returns the reference C5:C10 to SUM:
The formula in G7 is set up the same way. The only difference is the starting point:
When the number in G5 is changed, both formulas dynamically update and return a new result.
Year to date variation
Instead of hardcoding a value into the formula, you can use the COUNT function to count existing entries in a given column and return that count to OFFSET. A generic version of this formula would look like this:
where rng is a reference to a range that contains YTD values.