Sum a Dynamic Range

A common issue for Excel users is the ability to sum a range of cells that is constantly changing in height. As new rows are added to the data set, you require the sum formula to include these in its calculation.

Creating this dynamic range is an awesome skill that can then be applied to charts, PivotTables and other formulas. It ensures that these Excel features automatically adjust as new rows are added, or even removed from the data set.

Let’s look at a couple of solutions to this problem.

Use OFFSET in the SUM Formula

The OFFSET function can be used in Excel to return a reference based on dynamic criteria. It is one of the great Excel functions that is worth knowing. By nesting it inside of the SUM function it will pass the reference to the formula.

The OFFSET function needs to be told a starting reference, how many rows and columns to move, and also how high and wide the range of cells to return is. Armed with this information it can calculate the size of the range for you.

This information is entered as below;

=OFFSET(reference, rows, cols, [height], [width])

For example, we have a list of orders with the order amount stored in column G. We want to total the range from G2 to whatever the last order in that column is.

The formula below shows the OFFSET function returning the range of cells for SUM. The COUNTA function has been used to find how many rows high the list of data is. This creates the dynamic nature of the range.

Create a Dynamic Range Name

Nesting the OFFSET function inside the SUM formula is great. Another alternative though is to create a dynamic range name which you can then use in the SUM.

This defined name can then also be used in other formulas without the need to rewrite the OFFSET function. These names also make your formulas more meaningful and easier to read for users of the spreadsheet.

1. Click the Formulas tab on the Ribbon.
2. Click the Define Name button.
3. Enter a name for the defined name. This cannot includes spaces, begin with a number or be a cell reference.
4. In the Refers to: field enter the formula below.

=OFFSET($G$2,0,0,COUNTA($G:$G),1)

5. Click Ok.