If you need to sum a variable number of cells, such as calculating a year-to-date total for a monthly budget (e.g., from month 1 to month 5), here’s an efficient way to do it.
Step 1: Define Your Parameter
Start by defining the parameter. This could be the month number or the last day of the month, from which you can extract the month number using the formula MONTH().
Step 2: Use the SUM and OFFSET Functions
To achieve this, combine the SUM function with the OFFSET function. Here’s how OFFSET works:
- Offset: You provide a reference range, and
OFFSETshifts the starting point by a specified number of rows or columns. - Size Constraints: You can also define the number of rows and/or columns to include in the new range, which is key for our situation.
Example:
Here’s how it looks in action:
Let’s have a look at the formula :
There are certainly other ways to achieve this, but this method works well and is straightforward. Do note that you’ll need an up-to-date version of Excel to use the OFFSET function.
Going Deeper:
To make it more dynamic, you can also use the MATCH function to select a specific category, such as “Commerce” instead of “Marketing.” This adds flexibility, allowing you to fine-tune what data you want to sum.
This proves to be useful when you want to compare year-to-date Actuals with year-to-date Budget at the end of a month.