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

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

### 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: How to Create Dynamic Range Using Excel INDEX Function**

### 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 an **Excel offset dynamic range** that sum based on cell value. The **OFFSET f**unction 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**

**How to Create a Range of Numbers in Excel****Excel VBA: Dynamic Range Based on Cell Value****How to Autofill Dynamic Range Using VBA in Excel**

### 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**

### 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: How to Use Dynamic Range in Excel VBA**

**Download Practice Book**

Download the practice book here.

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

I am using this formula in conditional formatting in office 2019 version

=SUM(B$3:INDEX(B$3:B3,ROWS(B$3:B3)-1))<=B$35

but it throws error like "You may not use reference operators (such as union, intersection and ranges) or array constants for conditional formatting criteria"

However the same formula works fine in 2007 version of excel. Even in 2019 version, when i write the same formula in excel it works, but not in conditional formatting..

plz help

Hello,

UDAY KUMAR!It seems that the error message you received is related to the use of reference operators in the formula.

The formula you were using in the conditional formatting rule contains a reference operator and the INDEX function, which can be interpreted as an array constant. The error message you received indicates that the use of such operators and array constants is not allowed in conditional formatting criteria.

To avoid using the reference operator and array constant, you can use the INDIRECT function. Here’s an example formula that uses the INDIRECT function:

`=SUM(INDIRECT("B$3:B" & ROW()))<=$B$35`

The INDIRECT function takes a text string argument that specifies a cell reference and returns the value of the cell. By concatenating the starting and ending cell references with the ROW function, we can create a dynamic reference to the range we want to sum.

Hope this will help you. If not, can you please send me your excel file via email? ([email protected]).

Good Luck!

Regards,

Sabrina AyonAuthor, ExcelDemy.

Hello Sabrina Ayon

Thanks for replying. I tried ur solution but it did not help. I have sent u test file with complete problem on ur gmail ID. plz respond.

Thank you.

Uday Kumar

Dear Uday Kumar,

Good afternoon! First of all, thank you for the detailed description of the problem. What I have understood from your email, is that you want to highlight the cell where the sum of the distributed

X itemscrosses thePrevious Balance.Using the Excel formula in this situation becomes quite complicated and returns errors when used in the

Conditional Formattingoption. But running a simpleMacrocan achieve your desired output without any hassle.If you don’t know how to run a Macro, don’t worry. It’s not that complicated at all. Just follow the steps below, and you will be good to go.

Step 01: Create a Blank ModuleAt first, you will need to create a blank

Module. TheModuleis where we will write the code. Simply pressALT + F11on your keyboard to open the following window on your worksheet.Now, go to the

Inserttab and choose theModuleoption from the drop-down list.Step 02: Write and Run VBA CodeNow, a blank

Modulewill be created.Then, copy the following code and paste it into the blank

Module.After that, click on the

Saveicon.Following that, close the VBA window or simply press

ALT + F11. This will take you back to your worksheet.Now, carefully select the range of data.

Then, go to the

Developertab and click on theMacrosoption.Subsequently, the

Macrodialogue box will open.Now, select the

inventory_checkoption and click onRun.Then a window will appear asking you for the

Previous Balance. You need to enter the previous balance here and then clickOK.Boom! The highlighted cell will indicate your desired output.

You can change the

Previous Balanceaccording to your need and the highlighted cell will be changed accordingly.Things to Remember

If you don’t have the

Developeroption enabled thenfollow this article to enable it.Don’t forget to save the file as

Macro Enabled Workbook.I sincerely hope that this solves the issue you are facing. If any part of the solution is unclear to you, please let us know.

Regards

Zahid Hasan

ExcelDemy

Thanks Hassan for the Help..

There are certain reasons I do not want to use Macro, though I know how to use it. Secondly, as u can see in the test file sent to u in email, formula gives perfectly fine result in google spreadsheet, in excel cell but not in the conditional formatting area. So, I do not know what is going wrong. Do u have any opinion about that ??

Dear Uday Kumar,

Good day! I can comprehend how upsetting this situation could be. It took me a while to understand it, too. Hence, the portion of your formula that I have highlighted in the following figure is essentially what is causing the issue when doing

Conditional Formatting.It will display an error in the

Conditional Formattingwhen you specify a range using both acell referenceand aformula. As the goal of this formula is to add up to the cell that comes before the active cell, you can use the following formula instead. Here I simply replacedINDEX(B$3:B3,ROWS(B$3:B3)-1)byB2. The complete formula is:=AND(SUM(B$3:B3)>B$35, IF(ROWS(B$3:B3)<>1, SUM(B2:B$3)<=B$35, TRUE))Just paste this formula in the

Conditional Formattingoption and you will have your desired output as shown below.That should take care of your problem, I hope. If you run into any problems, please let us know.

Regards

Zahid Hasan

ExcelDemy

Hello Zahid Hasan,

Can u plz suggest a non macro method for a solution to above problems…actually there are certain reasons that I do not want to use it.

Hello Uday Kumar,

Kindly check the reply to your previous comment. We have given you a non-macro solution.

Thanks Sabrina Ayon, Hassan, Shamima Sultana for providing an intelligent solution by getting around the problem. It worked !

But for the sake of learning, compatibility issue between 2007 and 2019 remains to be quite baffling in current situation with the latest version not behaving properly. Its behaviour started appearing to me more as a magical problem than as a logical problem.

But any way thanks to the team!!