Formula to sum cells between but not including two endpoints

satheo

New Member
Joined
Jun 10, 2014
Messages
29
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 =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,643
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
Joined
Jun 10, 2014
Messages
29
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
Joined
Oct 14, 2016
Messages
3,643
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))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,129,991
Messages
5,639,415
Members
417,088
Latest member
jonstr101

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top