The Excel **SUM** function can calculate the summary of number values in a cell range. But when you add a new number cell before or after the cell range, the **SUM** function can not add the dynamically added number in the result. This article will tell you how to sum dynamic range in Excel.

### 1. Sum Dynamic Range In Excel Example?

- Below are the example data cells.

- Now input the formula
**=SUM(C2:C7)**in cell**D1**, then it will summarize the number values in the cell range**C2:C7**and show it in cell**D1**. - But when you add a new row after row 7, the formula will not add the newly added cell (
**C8**) number value.

### 2. Sum Dynamic Range In Excel Using Excel Table.

- You can use the Excel table to fix the above issue.
- Select the cell range
**A1:C7**, then click the Excel**Insert**tab —>**Table**item in the**Tables**group to convert the cell range into an Excel table ( you can refer to the article Excel Convert Table To Range Example And Vice Versa ). - Click the
**Table Design**tab on the Excel top menu bar, then you can change the Excel table name in the**Properties**group**Table Name**input text box. - In this example, I change the table name to
**Table_Sum**. - Now input the formula
**=SUM(Table_Sum[Sale Count])**in cell**D2**and press the enter key, then it will summarize all the cell number values in column**C**. - In this example the table name is
**Table_Sum**and the column name is**Sale Count**. - Right-click one row in the table and click the menu item
**Insert**to insert a new row ( the red row ) in the table like below.

- You will find that the summarized number value in cell
**D2**is changed automatically also, it includes the newly inserted row’s sale count number.

### 3. Sum Dynamic Range With Special Criteria In Excel Using INDEX & MATCH Function.

- We can also use the Excel
**INDEX**&**MATCH**function together to summarize the dynamic range number value in Excel. - Below are the example data cells, we add a
**Search Sale Count**number in cell**D1**, and a**Total Result NumberÂ**in cell**D4**.

- Input the formula
**=SUM(C2:INDEX(C2:C7, MATCH(D2, C2:C7,0)))**in cell**D4**. - Now if you input a number such as
**198**in cell**D2**, then it will show the number**1396**in cell**D4**. - The number
**1396**is the summary of the number value in the cell range**C2:C6**Â because cell**C6**contains the number**198**. - First, the function
**MATCH(D2, C2:C7,0)Â**will return the cell index number**6**( which matches the value in cell**D2**exactly )in the cell range**C2:C7**. - Then the function
**INDEX(C2:C7, MATCH(D2, C2:C7,0))Â**will return the matched cell**C6**from the cell range**C2:C7**. - Finally, the formula
**=SUM(C2:INDEX(C2:C7, MATCH(D2, C2:C7,0)))**will summarize the number value in cell range**C2:C6**and get the resulting number. - You can read the article How To Use Excel Match Function With Examples to learn more.

### 4. Sum Dynamic Range With Last None Empty Cell In Excel Using LOOKUP Function.

- If you want to summarize the cell number until the last non-empty cell in a column, you can read this example.
- You can first use the Excel
**LOOKUP**function to find the last non-empty Excel cell in a column ( please refer to the article Excel Find First / Last Non-Empty Cell In Row / Column Example ). - Then use the
**MATCH**function to get the last non-empty cell’s index number in the cell range. - Then use the
**INDEX**function to get the last non-empty cell. - Then use the
**SUM**function to summarize the column cell’s number values until the last non-empty cell. - In this example the
**LOOKUP**function is**LOOKUP(2,1/(NOT(ISBLANK(C2:C7))), C2:C7)**, it will find the last non-empty cell. - Then use the
**MATCH**function to get the last non-empty cell’s index number in the cell range**C2:C7**, the function is**MATCH(LOOKUP(2,1/(NOT(ISBLANK(C2:C7))), C2:C7), C2:C7,0)**. - Then use the
**INDEX**function to get the last non-empty cell, the function is**INDEX(C2:C7, MATCH(LOOKUP(2,1/(NOT(ISBLANK(C2:C7))), C2:C7), C2:C7,0))**. - Finally, use the
**SUM**function to summarize the column cell numbers from the first cell to the last non-empty cell, the formula is**=SUM(C2:INDEX(C2:C7, MATCH(LOOKUP(2,1/(NOT(ISBLANK(C2:C7))), C2:C7), C2:C7,0)))**.