Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)

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.


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.

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

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.

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

  • 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.

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

  • 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.

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

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

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

🔎 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.

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

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.

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

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

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

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


Similar Readings


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.

Excel Dynamic Sum Range Based on Cell Value with MATCH Function

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.

Excel Dynamic Sum Range Based on Cell Value with MATCH Function

  • Secondly, write a seller’s name in Cell E6. We have written the name of Chris.

Excel Dynamic Sum Range Based on Cell Value with MATCH Function

  • 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.

Excel Dynamic Sum Range Based on Cell Value with MATCH Function

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

Excel Dynamic Sum Range Based on Cell Value with MATCH Function

🔎 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.

Create Dynamic Sum Range Based on Another Cell Value in Excel

  • 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.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo