There are a couple of ways to create a dynamic SUM formula in Excel. Here are two common methods:

Method 1: Using OFFSET and COUNTA functions

This method is useful when you want to sum a range of cells that expands or contracts as you add or remove data.

Formula:

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

Explanation:

* `cell_reference`: This is the starting cell of your data range (e.g., A2).
* `0, 0`: These specify no offset from the starting cell in terms of rows and columns (stays in the same cell).
* `COUNTA(column_to_count)`: This counts the number of non-blank cells in the column you want to sum (e.g., column A). Subtract 1 to exclude the header row (assuming you have a header).
* `1`: This specifies the width of the range (always 1 for summing a single column).

Benefits:

* Automatically adjusts the range to include new data.
* Easy to understand and implement.

Drawbacks:

* Can be volatile (recalculates frequently), impacting performance on large datasets.

Method 2: Using SUMPRODUCT with dynamic range references

This method is less volatile than the OFFSET method and can be used for more complex scenarios like summing specific criteria within a dynamic range.

Formula:

```excel
=SUMPRODUCT((criteria_range=criteria)*(data_range))
```

Explanation:

* `criteria_range`: This is the range containing your criteria for summing (e.g., column B with values like "Fruit").
* `criteria`: This is the specific criterion you want to match (e.g., "Fruit").
* `data_range`: This is the range containing the data you want to sum (e.g., column C with prices).

Benefits:

* Less volatile than OFFSET method.
* Can be used for conditional summing based on criteria.

Drawbacks:

* Can be more complex to set up for beginners.

Tips:

* Remember to adjust the cell references and criteria based on your specific data layout.
* Use absolute cell references ($A$1) for criteria if they are not located in the same row or column as the formula.
* Consider using helper columns for complex criteria to improve readability.

These are two common approaches to creating dynamic SUM formulas in Excel. The best method for you will depend on your specific needs and data structure.