summing values between cells with the same name

[marcs]

Say a column (say column A) has cells with the following values and I want to end up with the sum of the values between each marker in column B.The marker could be a text value or a colorindex but would always be the same. The numbers in column A vary and get put there by a macro and get deleted by a macro. The markers also come and go by macro. (The marker will actually represent a wall and the numbers will be door and window areas that need to be deducted from the wall area).
Is there a way to do this which does not involve problems with formulas not adjusting properly as lines are added or deleted?

A B
__________________
marker 14
4
7
3
marker 8
2
6
marker 12
8
1
3
marker



All help appreciated

Heimir.

 

[PaddyD]

with your data in a1:

=IF(A1="marker",(SUM(A1:INDEX($A$1:$A$100,MIN(IF(A2:$A$100="marker",ROW(A2:$A$100)))))),"")

in b1, entered with control + shift + enter, not just enter & copied down

 

[marcs]

Hi PaddyD,

thanks for that. It seems to be working. I was having trouble with pasting and deleting and the formula not updating properly. I'll play around with it

cheers

Heimir

 

[Peter_SSs]

Maybe something like this, where the $1000 is below where your data is ever likely to get. B2 formula copied down to the bottom of the data (or beyond where the bottom of the data will ever reach)

Excel Workbook
	A	B
1	marker	14
2	4
3	7
4	3
5	marker	8
6	2
7	6
8	marker	12
9	8
10	1
11	3
12	marker	0
Sum Between Markers

 

[marcs]

I'm getting the problem that if you insert a new row directly under a row starting with 'marker' and then you copy and paste the formula into that new row the new row works well but the old row just above it does not update its formula. Anyone got any suggestions?

Heimir

 

[Peter_SSs]

See if this solves the problem:

Excel Workbook
	A	B
1	marker	14
2	4
3	7
4	3
5	marker	8
6	2
7	6
8	marker	9
9	5
10	1
11	3
12	marker	0
Sum Between Markers

 

[marcs]

oops - dual post

 

[marcs]

Hi Peter,

thanks for that. It is elegant and simple and, with hindsight, rather obvious.
I'm still not sure what to do with this problem of updating. As I said, if you insert a row called marker and copy and paste the formula into it (all via a macro) then the line above does not update and tries to include the new line. Maybe I just write a macro along the lines of 'after inserting new line, if the value above is marker then delete the formula to the right of it' ?
I was hoping excel could recognise the new line and update it automatically a bit like it does when you delete a line

cheers

Heimir