Function (or VBA) to calculate just the above section?

[DerekK]

Good day, Excelsiors,

I have a sheet that has multiple "sections," and I need a formula which will calculate only the section above it. I have a formula that works but I have to continually update it over and over again and it gets time consuming. I'm sure there's a way to simplify it but I can't seem to figure out how.

Here is a simplified version of my sheet:

1 Section heading1
2 [blank]
3 [random number]
4 [random number]
5 =SUM(OFFSET($A$1,2,,(ROW()-ROW($A$1)-2)))
6 [blank]
7 Section heading1
8 [blank]
9 [random number]
10 [random number]
11 [random number]
12 =SUM(OFFSET($A$7,2,,(ROW()-ROW($A$7)-2)))

I've tried an array formula using ROW([something dynamic])*OFFSET()="Section heading1" but I can't seem to make the ROW element dynamic.

I know it seems silly but I have to repeat this over and over, and it's eating me inside!

Hope you can help!

Thanks,



Derek

 

[DerekK]

Sorry, this would make more sense if I explained that I don't know how many random numbers there will be and I continually have to adjust the number of random numbers summed, by moving the formula down.