Here are two common approaches to creating a dynamic SUM formula using the OFFSET function in Excel:

Method 1: OFFSET and COUNTA

This method uses the OFFSET function to define a dynamic range and COUNTA to determine the number of cells with data within that range.

Formula:

```excel
=SUM(OFFSET(cell_reference, 0, 0, COUNTA(row_reference)-1, 1))
```

Explanation:

- `cell_reference`: This is the starting cell of your data range (e.g., A1).
- `row_reference`: This is a reference to a cell containing a value that determines the length of your data range (e.g., B1 might contain the number of items you have data for).
- `COUNTA(row_reference)-1`: This part calculates the number of cells with data in the `row_reference` (excluding the header if present). The -1 subtracts 1 to account for the starting cell itself.
- `OFFSET(...)`: This function offsets the `cell_reference` by 0 rows and 0 columns (maintaining the starting cell), then uses the calculated number of rows from COUNTA and sets the width to 1 column to create the dynamic range for the SUM function.
- `SUM(...)`: This function sums the values within the dynamically created range.

Method 2: OFFSET and INDIRECT

This method uses OFFSET to define a dynamic range and INDIRECT to convert a text string into a cell reference.

Formula:

```excel
=SUM(OFFSET(cell_reference, 0, 0, ROW()-ROW(cell_reference)+COUNTA(row_reference)-1, 1))
```

Explanation:

- `cell_reference`: This is the starting cell of your data range (e.g., A1).
- `row_reference`: This is a reference to a cell containing a value that determines the length of your data range (similar to method 1).
- `COUNTA(row_reference)-1`: Similar to method 1.
- `ROW()-ROW(cell_reference)`: This part dynamically calculates the relative row offset based on the current row where the formula is placed.
- `OFFSET(...)`: Similar to method 1, it uses the calculated offsets to create the dynamic range for the SUM function.
- `SUM(...)`: This function sums the values within the dynamically created range.

Benefits of Dynamic SUM with OFFSET:

- Automatically adjusts the sum range as data is added or deleted.
- More flexible than fixed ranges, especially for growing datasets.

Important Notes:

- These formulas assume your data starts in row 1. Adjust the row numbers if your data starts elsewhere.
- Be mindful of circular references when using the INDIRECT method.

Choose the method that best suits your needs and data structure. Both methods achieve dynamic summation using the OFFSET function with different approaches.