Sum Adjacent Column Based on Value in Subsequent Rows.

[d4vew]

Hi,

I'm hoping you can help. I'm trying to automatically add some values of a Bill of Materials. I have numbers in column A which represent the level in the Bill of Materials, column B is the price, column C is where to totals should go. The criteria is that if the next row is greater than the current row, then the value is entered into column C of the current row, additional rows that are the same value as that row are also added in the same column C. The addition stops when a row is greater or less than the value being checked.
(I'm expecting blanks as shown below)

1​	10​	10​	=B2
2​	12​	27​	=B3+B4
3​	13​
3​	14​	31​	=B5+B6
4​	15​
4​	16​
2​	17​
2​	18​	60​	=B9+B10+B11
3​	19​
3​	20​
3​	21​

Many thanks, D

 

[RobP]

Not sure to understand well from your description. If the top row is row 1, and I am looking at col A, B, C (& D with your comments inside) :

How does Cell C1 = 10 (when you say it should = B2 (which for me would be 12 ?).

When you say the next row > current row, you mean in column A or some other criteria ?

I presume somehow you are trying to get a BOM cost per level ? More info would be useful...

many thanks
Rob

 

[d4vew]

Hi RobP, Thanks for trying to understand what I'm after, I was meant to write 12 in C2. I don't seem to be able to edit my original post.

This is for a BoM per costed level, the row's that don't have all the info, I'm okay with as I have worked out that part of the formula. The BoM export has a feature that adds up the lower levels twice and this is aiming to correct that.

Does the following make more sense, or have I clouded it further?
If value in Column A Row 1 = X and value in Column A Row 2 = X+1, then place the value of Column B Row 2, in Row 1 Column C.
If value in Column A Row 3 = Column A Row 2, then add the value in Column B Row 3 to the value in Row 1 Column C.
Repeat until the value in Column A Row ? <>Column A Row 2. Where the row checking starts again.

 

[RobP]

Hi, just applying your logic (If value in Column A Row 1 = X and value in Column A Row 2 = X+1, then place the value of Column B Row 2, in Row 1 Column C.)
would give me this for the first three rows, as each time they are increasing by 1 right ? :

Book1
	A	B	C	D
1	1	10	12
2	2	12	13
3	3	13	14
4	3	14
5	4	15
6	4	16
7	2	17
8	2	18
9	3	19
10	3	20
11	3	21
Sheet1

which is not your expected outcome I think (from the first post).
I then don't follow why you jump from Row 1, straight to Row 3 .. I mean what about Row2 ? and what happens if things are not = or > ?

You have to think that any logic applied here needs to go to every row of cells in Col C.

thanks
Rob

 

[RobP]

Not sure if it helps your cause, but this formula for example would cost all products at each level and give you a total per level :

Book1
	A	B	C
1	1	10	10
2	2	12	47
3	3	13	87
4	3	14	87
5	4	15	31
6	4	16	31
7	2	17	47
8	2	18	47
9	3	19	87
10	3	20	87
11	3	21	87
Sheet1

Cell Formulas
Range	Formula
C1:C11	C1	=SUM(FILTER(B$1:B$11,$A$1:$A$11=A1))