In this article, we will learn to create a **dynamic sum range based on cell value** in **excel**. Often, we need to sum a range based on a cell value in excel. If we change the value of the cell, the sum also automatically updates. Today, we will discuss 4 easy ways to define dynamic sum range in excel based on cell value.

**Table of Contents**hide

## Download Practice Book

Download the practice book here.

## 4 Ways to Create Dynamic Sum Range Based on Cell Value in Excel

### 1. Use INDEX Function to Create Dynamic Sum Range Based on Cell Value in Excel

In the first method, we will use the **INDEX Function** and the **SUM Function** to create a dynamic sum range based on cell value. Here, we will use a dataset that contains information about the **Sales Amount** of the first two months of some **Sellers**.

Letâ€™s follow the steps below to learn this method.

**STEPS:**

- In the first place, to explain the method, we have inserted the
**row numbers**in**Column C**and**column numbers**in**Row 6**.

- In this case, it means
**Row 7**of the sheet is the**first row**and**Column D**is the**first column**of our**array (D7:E12)**. - Secondly, write
**Rows, Columns,**and**Sum**in**Cell G5**to**G7**like the picture below. We will type the row & column numbers in**Cell H5**&Â**H6**.

- Here,
**Rows = 4**&**Columns = 1**is denoting the values**Cell D7**to**D10**. - Thirdly, select
**Cell H7**and type the formula:

`=SUM(D7:INDEX(D7:E12,H5,H6))`

- Then, hit
**Enter**to see the result in**Cell H7**.

- Finally, if we change the values of
**Rows**and**Columns,**the**Sum**will be dynamically updated.

**đź”Ž How Does the Formula Work?**

**INDEX(D7:E12,H5,H6)**

The **INDEX Function **returns a value or reference of the cell at the intersection of a particular row and column, in a given range. We have used the **INDEX Function** to get the last cell. Here, the **first argument **is the **array(D7:E12)**. The **second argument (H5) **denotes the **rows **and the **third argument (H6)** denotes the **columns **of the array.

**SUM(D7:INDEX(D7:E12,H5,H6))**

The **Sum Function** is just summing up the values starting from **D7**. We used the **INDEX Function **to call the last cell of the selected range.

So, if we simplify, the formula will be:

`=SUM(first cell:INDEX(array,rows,columns))`

**Read More: Excel VBA: Dynamic Range Based on Cell Value (3 Methods)**

### 2. Apply OFFSET Function to Define Dynamic Sum Range Based on Cell Value

We will use the **OFFSET Function** and the **SUM Function** to define a dynamic sum range based on cell value. The **OFFSET Function** returns a reference to a range that is a given number of rows and columns from a given reference. Here, we will use the previous dataset.

Letâ€™s pay attention to the steps below to learn more.

**STEPS:**

- In the beginning, select
**Cell G7**. - After that, type the formula:

`=SUM(OFFSET(C6,0,0,G5,G6))`

- Then, hit
**Enter**to see the result in**Cell G7**.

Here, the **OFFSET Function** returns a range that starts from **Cell C6**. **Cell G5 **and **G6 **define the **height **and **width **of the range.

- Finally, if we change the values of
**Cell G5**and**G6**, the**Sum**will be automatically updated.

- Here,
**Rows = 4**&**Columns = 2**indicates the highlighted values of the range.

**Read More:Â Excel OFFSET Dynamic Range Multiple Columns in Effective Way**

**Similar Readings**

**Excel Dynamic Named Range [4 Ways]****Excel Dynamic Named Range Based on Cell Value (5 Easy Ways)****How to Create a Range of Numbers in Excel (3 Easy Methods)**

### 3. Excel Dynamic Sum Range Based on Cell Value with MATCH Function

We can also use the **MATCH Function****, INDEX Function, **and **SUM Function **together to define a dynamic sum range based on cell value. The **MATCH Function** returns the relative position of an item in an array that matches a specified value in a specified order. Here, we will use the same dataset excluding the sales amount of the **February **month.

Letâ€™s observe the steps below to know more.

**STEPS:**

- Firstly, create cells to write the
**sellerâ€™s name**and the**Sum**We have created these cells in**Column E**.

- Secondly, write a sellerâ€™s name in
**Cell E6**. We have written the name of**Chris**.

- After that, select
**Cell E10**and type the formula:

`=SUM(C6:INDEX(C6:C11,MATCH(E6,B6:B11,0)))`

- Then, press
**Enter**to see the result.

- Finally, if we change the name of the seller, the
**Sum**will be dynamically updated.

**đź”Ž How Does the Formula Work?**

**MATCH(E6,B6:B11,0)**

The **MATCH Function** finds the position of the selected seller in the range of the sellers.

**INDEX(C6:C11,MATCH(E6,B6:B11,0))**

The **INDEX Function** returns a value from the **range C6:C11**. The second argument of the **INDEX Function **is the position of the element that we need to return.

**SUM(C6:INDEX(C6:C11,MATCH(E6,B6:B11,0)))**

But, if we put the previous **INDEX Function **formula inside the **SUM Function**, the **INDEX Function **will return the reference of the cell and then, sum it up.

**Read More:**Â **How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)**

### 4. Create Dynamic Sum Range Based on Another Cell Value in Excel

We can also define a dynamic sum range using the **INDIRECT **and the **CONCATENATE **functions. Here, we will use the previous dataset.

#### 4.1 Insert INDIRECT & CONCATENATE Functions Together

In this sub-method, we will use the **INDIRECT **and the **CONCATENATE Functions **together. The **INDIRECT Function** returns the reference specified by a text string and the **CONCATENATE Function** joins several text strings into one text string. Letâ€™s follow the steps below.

**STEPS:**

- Firstly, create the dataset structure like the picture below.

- Here,
**9**in**Cell F6**refers to**ROW 9**of the sheet.

- After that, select
**Cell F7**and type the formula:

`=SUM(C6:INDIRECT(CONCATENATE("C",F6)))`

- Then, hit
**Enter**to see the result.

Here, **C6 **is the first cell of the column that we need to sum, **Column C **is the column where we need to perform the **sum **operation, and we sum up based on **Cell F6**. The **INDIRECT(CONCATENATE(â€śCâ€ť,F6) **part of the formula returns the last cell of the range we need to sum up.

- Finally, if we change the value of
**Cell F6,**the result is automatically updated.

- In addition, if you want to sum two columns, use the below formula:

`=SUM(C6:INDIRECT(CONCATENATE("C",F6)),D6:INDIRECT(CONCATENATE("D",F6)))`

**đź”Ž How Does the Formula Work?**

**C6:INDIRECT(CONCATENATE(â€śCâ€ť,F6))**

This is the first argument of the **SUM Function**. It refers to the range of **Column C **that we want to sum.

**D6:INDIRECT(CONCATENATE(â€śDâ€ť,F6))**

Here, it is the second argument of the **SUM Function**. It refers to the range of **Column D **that we need to sum.

**SUM(C6:INDIRECT(CONCATENATE(â€śCâ€ť,F6)),D6:INDIRECT(CONCATENATE(â€śDâ€ť,F6)))**

Now, the **SUM Function** is just summing up the ranges of **Column C **and **D**.

#### 4.2 Use INDIRECT Function Only

We can again use the **INDIRECT Function** and the **SUM Function** for this purpose. In this sub-method, we have used the same dataset to demonstrate the steps. Letâ€™s follow the steps below to know more.

**STEPS:**

- Firstly, create the dataset structure like the picture below.

- Here, we will sum the values of
**Row 6**to**Row 11**of**Column C**. - After that, select
**Cell E10**and type the formula:

`=SUM(INDIRECT("C" &E5& ":C" &E6))`

- Hit
**Enter**to see the result.

Here, we have used the **INDIRECT Function **to create the variable range of cell references that we want to sum. Then, we have embedded it inside the **SUM Function**.

- Finally, if we change the value of
**Cell E6,**the**Sum**will automatically be updated.

**Read More: Excel Dynamic Range Based on Cell Value**

## Conclusion

In conclusion, we can say that have demonstrated 4 easy and quick methods to define dynamic sum range based on cell value in excel. I hope these methods will help you to sum a range based on cell value. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.