Having Actuals, Budget and Variances for each month going across the page is a very common reporting construct. Here is an easy way to sum the correct YTD values as the year progresses.

In the image below you can see the structure we are working with.

Note November has budget figures but no actuals. All the other future months have their budget as well.

The Challenge

How can we only add up the correct budget values for the current month YTD figures? In the case only the first four budget months.

The current month is shown in A1. It is an input.

In the image below we have the YTD columns visible.

The value in cell BB1 is a formula to calculate how many months in the Australian financial year. The formula in cell BB1 is.

=MONTH(A1)+IF(MONTH(A1)>6,-6,6)

Cell A1 is an input cell and updated each month as the year progresses.

The formula for YTD budget sales in cell AY3 that sums the correct budget columns is shown in the image below.

The formula is.

=SUM(OFFSET($A3,0,SEQUENCE(1,$BB$1,2,4)))

This can be copied down for Cost of Sales (row 4) and to the left (column AX) for Actuals.

How it works

Let’s start with the OFFSET function. It has its anchor cell as A3. It offsets zero rows. The number of columns to offset from A3 is determined by the SEQUENCE function. The SEQUENCE function creates a one row sequence of numbers that starts at 2 and increments by 4.

How many numbers that are generated is determined by the month number in the financial year. In this case 4 (value in cell BB1) numbers are generated for October 2024. The numbers are 2, 6,10 and 14.

These are the column offsets from cell A3 to select the first four budget columns.

The OFFSET function will refer to the four cells C3, G3, K3 and O3. The SUM function will then add the cells up.     

Note the actuals formula could just add up each of the actual columns because they are zero for future months.

As the financial year progresses the month number will increase in cell BB1, and the number of budget cells added up will also increase.