summing values between cells with the same name

marcs

Active Member
Joined
May 3, 2005
Messages
420
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.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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
 
Upvote 0

marcs

Active Member
Joined
May 3, 2005
Messages
420
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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
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
AB
1marker14
24
37
43
5marker8
62
76
8marker12
98
101
113
12marker0
Sum Between Markers
 
Upvote 0

marcs

Active Member
Joined
May 3, 2005
Messages
420
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
 
Upvote 0

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
59,294
Office Version
  1. 365
Platform
  1. Windows
See if this solves the problem:

Excel Workbook
AB
1marker14
24
37
43
5marker8
62
76
8marker9
95
101
113
12marker0
Sum Between Markers
 
Upvote 0

marcs

Active Member
Joined
May 3, 2005
Messages
420
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
 
Upvote 0

Forum statistics

Threads
1,191,718
Messages
5,988,264
Members
440,146
Latest member
rgomes8

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