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

We will use a dataset that contains information about the **Sales Amount** of the first two months of some **Sellers**.

**STEPS:**

- Create a new column for the
**row numbers**in**Column C**and a new row for**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)**. - Write
**Rows, Columns,**and**Sum**in**Cell G5**to**G7**like the picture below. - Type the row and column numbers in
**cells H5**and**H6**.

**Rows = 4**and**Columns = 1**denote the values**Cell D7**to**D10**.- Select
**Cell H7**and type the formula:

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

- Hit
**Enter**to see the result in**Cell H7**.

- 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 reference the last cell of the selected range.

When simplified, the formula becomes:

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

**Read More: **How to Create Dynamic Range Using Excel INDEX Function

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

Let’s use the same dataset as in the previous method.

**STEPS:**

- Select
**Cell G7**. - Copy this formula:

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

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

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

- Here,
**Rows = 4**and**Columns = 2**indicate the highlighted values of the range.

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

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

Here, we will use the same dataset but exclude the sales amount of **February **month.

**STEPS:**

- Create cells to write the
**seller’s name**and the**Sum.**We have created these cells in**Column E**.

- Write a seller’s name in
**Cell E6**. We have written**Chris**.

- Select
**Cell E10**and type the formula:

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

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

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

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.

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

We will use the previous dataset for this method.

#### 4.1 Insert INDIRECT & CONCATENATE Functions Together

**STEPS:**

- Create the dataset structure like the picture below.

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

- Select
**Cell F7**and type the formula:

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

- 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 cell reference for the end of the sum array, combining C with the number in the F6 cell.

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

- 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

INDIRECT can also be used to return a range, and it already accepts string values.

**STEPS:**

- Create the dataset structure like the picture below.

- This will sum the values of
**Row 6**to**Row 11**of**Column C**. - 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**.

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

**Download Practice Book**

Download the practice book here.

## Related Articles

- How to Create a Range of Numbers in Excel
- Excel Dynamic Named Range Based on Cell Value
- Data Validation Drop Down List with Excel Table Dynamic Range

**<< Go Back to Dynamic Range | Named Range | Excel Formulas | Learn Excel**

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!!

dear sir i have amount in single cell like Dr $15.00 Athena $3.00 and how i am sum this amount in one cell

Hello

Muhammad Afzaal Jutt,You need to use a combination of functions like

SUBSTITUTE,TEXTJOIN,FILTERXML, andSUM. Currently, Excel does not directly provide a simple formula for this without VBA.You can use the following formula:

`=SUM(FILTERXML("<t><s>" & SUBSTITUTE(SUBSTITUTE(A1, " ", "</s><s>"), "$", "") & "</s></t>", "//s[number(.)=.]"))`

Regards

ExcelDemyThis is great! Thank you sir. I started off with Method 1 (using INDEX) in my file, but the file slowed down noticeably. I then tried Method 3 (using OFFSET), it appears to be faster.

Hello

David,You are most welcome. Thanks for your feedback! The

INDEXis an array function it recalculates every time there’s a change in the referenced data, especially if you’re working with large datasets for these reasons the file may slowdown. ThoughOFFSETis volatile function, but in some cases, calculates faster based on the data structure and size.If your dataset is extensive, you might see better performance with

OFFSET.It’s great to hear that

OFFESTis more efficient for your data type. Keep learning Excel with ExcelDemy!Regards

ExcelDemy