In older versions of Excel, you might 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.
As new values are added todata, TAKE will continue to return the lastnvalues to SUM.
What should you do if new data is being added on an ongoing basis?
The result is a “trimmed” range that only includes data from the used portion of the range.
you’re free to read more about the TRIMRANGE functionhere.
The COUNT function returns the number of numeric values in data.
We subtract E5 to “back up” to the correct cell.
Forcols, we provide 0 since we don’t want a column offset.
We provide E5 forheight, since we want our final range to bencells tall.
We don’t need to provide a value for the optionalwidthargument, sincewidthwill inherit fromreference.
This makes the formula more error-prone sincedataand C5 are disconnected.
Now, as long as the reference todatais correct, the formula will work properly.
One limitation of the formulas above is that they won’t automatically include new data added to the range.
you’re able to create a dynamic named rangewith the OFFSET functionorwith the INDEX function.
Another (simpler) option is touse an Excel Tableto create the range.
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.
TRIMRANGE Function
The Excel TRIMRANGE function removes empty rows and columns from a range of data.
The result is a “trimmed” range that only includes data from the used portion of the range.
OFFSET is handy in formulas that require a dynamic range.
you might use INDEX to retrieve individual values, or entire rows and columns.
The MATCH function is often used together with INDEX to provide row and column numbers….
