# Formula to sum cells between but not including two endpoints

#### satheo

##### New Member
I'm trying to have a column of subtotals, that need to be added up but I want to be able to insert a row anywhere within that column and have the grand total update accordingly - the sum formulas adjust if I add a row within the subtotal rows but not on either end of it (so if I insert above "subtotal 1" or below "subtotal 4" below). Is there an easy way around this? Like can I sum between "title text" and "grand total"?

 Title text subtotal 1 subtotal 2 subtotal 3 subtotal 4 grand total

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### Tetra201

##### MrExcel MVP
See if the following works for you:
Excel Formula:
``=SUM(INDEX(A:A,MATCH("Title Text",A:A,0)+1):INDEX(A:A,MATCH("Grand Total",A:A,0)-1))``

#### satheo

##### New Member
See if the following works for you:
Excel Formula:
``=SUM(INDEX(A:A,MATCH("Title Text",A:A,0)+1):INDEX(A:A,MATCH("Grand Total",A:A,0)-1))``
Thank you, I think this is close but the Grand Total cell is just a formula - I saw another thread where someone used:
Excel Formula:
``=SUM(INDIRECT(ADDRESS(1,COLUMN())&":"&ADDRESS(ROW()-1,COLUMN())))``
to sum all cells above a certain cell, which kinda worked but I need that to stop at the Title Text cell as I have things above it. Sorry I'm clearly not good with formulas but how would I combine these (I did try but dont really understand what is going on with these)? Can assume these are all in column A, rows 1-6 for now.

#### Tetra201

##### MrExcel MVP
The INDIRECT function is volatile, and it is advisable to avoid using it whenever possible.

Here is a modified formula for you to try:
Excel Formula:
``=SUM(INDEX(A:A,MATCH("Title Text",A:A,0)+1):INDEX(A:A,MATCH(A6,A:A,0)-1))``

Replies
12
Views
102
Replies
2
Views
41
Replies
3
Views
103
Replies
0
Views
92
Replies
1
Views
178

1,129,795
Messages
5,638,373
Members
417,024
Latest member
Mrpica01

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back