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.

**A****B****C****D****1****Sale Item****Unit Price($)****Sale Count****2**iPhone 1000 100 **3**iPad 900 98 **4**Macbook Pro 1200 300 **5**Macbook Air 1300 600 **6**Mac Mini 1200 198 **7**Apple Watch 800 1000 - 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 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.

**A****B****C****D****1****Sale Itemâ‡“****Unit Price($)â‡“****Sale Countâ‡“****2**iPhone 1000 100 **=SUM(Table_Sum[Sale Count])****3**iPad 900 98 **4**Macbook Pro 1200 300 **5**iMac 100 100 **6**Macbook Air 1300 600 **7**Mac Mini 1200 198 **88**Apple Watch 800 1000 - 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**.

**A****B****C****D****1****Sale Item****Unit Price($)****Sale Count****Search Sale Count****2**iPhone 1000 100 198 **3**iPad 900 98 **Total Result Number****4**Macbook Pro 1200 300 1396 **5**Macbook Air 1300 600 **6**Mac Mini 1200 198 **7**Apple Watch 800 1000 - 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)))**.