Sum above to specific cell: Looking for formula

[PaulLor89]

SumAbove.xls

I'm searching for a formula to help automate the process of summing up values from a certain point downwards/upwards. Currently using this formula but is not exactly what I want.

=SUM(INDIRECT("L1:L" & ROW()-1))

Problem is, whenever I want to insert a new record/row/entry I would have to redirect the position or starting point of the summation. Is there a sum formula where I can designate a start point (absolute, fixed position, e.g. $L$1)?

I have hundreds of rows which are categorized specifically for organizational purpose and reference. As mentioned, I will be inserting new rows underneath a previous entry (e.g. column row 14). I would like the a formula that will sum the values in column L for the specific group of data (E.g. Afraxis). Let me know if you're still confused.

**Any recommendations or suggestion of how I can do this better is welcome!! Thanks!

[Moo the Dog]

Your formula seems to expand as needed in my workbook... but you could try this in L17:

=SUM(INDIRECT("L1:" & ADDRESS(MATCH(99^99,L1:L16),COLUMN(),4)))

That will find the cell containing the last numeric value in column L rows 1:16. As you insert another row, the formula will adjust to include rows 1:17, etc.

- Moo

* Scratch that.. now I see what you are getting at. It's not the first set that is the problem, it is the sets below the first set. =)

[PaulLor89]

Thanks for the response Moo the Dog. The attached excel is only a snippet of the actual data that I'm working with. Do you think there is a formula that would more universal?

Ideally, a formula similar to Sum(above), for example in the sample data, sum(above) cell L33 and ending in L19? But how can I make it stop at a specific place or cell? Appreciate the help!

[PaulLor89]

Thanks for the response Moo the Dog. The attached excel is only a snippet of the actual data that I'm working with. Do you think there is a formula that would more universal?

Ideally, a formula similar to Sum(above), for example in the sample data, sum(above) cell L33 and ending in L19? But how can I make it stop at a specific place or cell? Appreciate the help!

[WinePaul]

Maybe I'm missing something.

Just name the range,( i.e. Rows 1:17 as Afraxis). Then just

=sum(Afraxis)

You can insert as many rows as you like, and they just become part of the range.

[PaulLor89]

Yup, you're right. That's what I've been doing. Looking for a better method to make the total change instantly after I entered new rows/data. I think your method would be re-calibrating the formula to include the new rows. It doesn't take that long to change the range of data to sum but I would need to do it repeatedly, which is tedious and time consuming considering that I have a lot of necessary new entries. Thanks!

[PaulLor89]

Any suggestions?

[popipipo]

Please Login or Register  to view this content.

Is this what you want?

[PaulLor89]

Hi Popipipo, that will work. Changed a few things but sumif will suffice. Thank you.