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.

## 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.

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

- Click
**Ok**.

I like your examples. I have tried to use your dynamic sum function (but I needed to use sumif as I needed to use a criteria. THe page that I am using is an expanding data set. The problem is that the data set increase by the use of a macro which INSERTS A NEW ROW AT THE TOP before copying the data into the row. The dynamic formula changes with each new row inserted I will paste my code below

ReplyDelete=SUMIF(OFFSET(SavedPayData!$A$3,0,0,COUNTA(SavedPayData!A:A),1),D8,OFFSET(SavedPayData!$M$3,0,0,COUNTA(SavedPayData!M:M),1))

In this formula it was originally referring to $A$2 etc