Formula to sum cells between but not including two endpoints

[satheo]

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

 

[Tetra201]

See if the following works for you:

=SUM(INDEX(A:A,MATCH("Title Text",A:A,0)+1):INDEX(A:A,MATCH("Grand Total",A:A,0)-1))

 

[satheo]

See if the following works for you:

=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:

=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]

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

Here is a modified formula for you to try:

=SUM(INDEX(A:A,MATCH("Title Text",A:A,0)+1):INDEX(A:A,MATCH(A6,A:A,0)-1))