Explanation
In the example shown, we have a list of amounts by month.
The goal is to dynamically sum values through a given number of months using a variablenin cell E5.
In other words, we want to sum the firstnvalues starting at cell C5.
In older versions of Excel, it’s possible for you to use theOFFSET function.
Both approaches are explained below.
TAKE function
The TAKE function returns a subset of a givenarray.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Next, we provide 0 forrowsand 0 forcolssince we don’t want a row offset or a column offset.
We don’t need to provide a value for the optionalwidthargument, since width will be automatically inherited fromreference.
In this configuration, OFFSET will return a reference to C5:C10.
The number of rows and columns to return is provided by separaterowsandcolumnsarguments.
Rows and columns can be extracted from the start or end of the given array.
SUM Function
The Excel SUM function returns the sum of values supplied.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
OFFSET is handy in formulas that require a dynamic range.
