SOLVED

# Sum a range of cells in column, as long as a condition is OK

Copper Contributor

# Sum a range of cells in column, as long as a condition is OK

Hello,
I would like to sum cells of a column, AS LONG AS a condition is verified.

In the example here below, I need to sum the cells of "Value" column, as long as the "Level" is equal or above the "Level" of current cell.

I am supposed to find out the same values as in colomn "Sum (Objective)"

 Line # Level Value Sum (objective) Formula (Sum of values) explanation 8 0 1 11 =sum(Line8:Line18) 9 1 1 5 =sum(Line9:Line13) 10 2 1 2 =sum(Line10:Line11) 11 3 1 12 2 1 2 =sum(Line12:Line13) 13 3 1 14 1 1 5 =sum(Line14:Line18) 15 2 1 2 =sum(Line15:Line16) 16 3 1 17 2 1 2 =sum(Line17:Line18) 18 3 1

I hope this is clear enough.

Thank you in advance for you help.

4 Replies
best response confirmed by Ace166 (Copper Contributor)
Solution

# Re: Sum a range of cells in column, as long as a condition is OK

Perhaps

if I understood the logic correctly.

# Re: Sum a range of cells in column, as long as a condition is OK

Unfortunately, it looks like I have simplified my table too much.
I am sure, it si something close to what you have proposed, but net yet good.
Here below, more details. (Sorry, I did not find how to attach a file)

 A B C D E F G H I J K L 1 Explanation of the Bill of Material table 2 3 Line Line Above current Level of BoM Parent Article Current Article Type Sub-Total Line Cost Obective Sum 4 0 -1 0 CN0 Assy O 20,00 € 1 475,59 € 5 1 0 1 CN0 CN1,1 Assy O 50,00 € 52,14 € 6 2 1 2 CN1,1 CN2,01 Purchase 1,00 € 7 3 1 2 CN1,1 CN2,02 Purchase 0,01 € 8 4 1 2 CN1,1 CN2,03 Purchase 0,01 € 9 5 1 2 CN1,1 CN2,04 Purchase 0,01 € 10 6 1 2 CN1,1 CN2,05 Purchase 0,10 € 11 7 1 2 CN1,1 CN2,06 Purchase 0,01 € 12 8 1 2 CN1,1 CN2,07 Purchase 1,00 € 13 14 15 Col. B Line = Number of line / Line number in the BoM 16 Col. C Line Above Current = Line of which the current line is depending 17 Col. D Level of BoM = Level in the descending "tree" of the BoM 18 Col. E Parent Article = Article number above the current Article 19 Col. F Current Article = Article(s) depending of a (specific) parent article 20 Col. G Type ==> can be "Assy", which implies a Sub-Total of all the lower level line costs  (only if lower level exist) / can be "purchase", "Inter-Site"==> no sub-total 21 Col. H Sub-Total = if "O" ==> lines for which we need to calculate the sub-total cost (for all levels above the current one) 22 Col. I Actual current line cost 23 Col. K Manual cost calculation for lines with "Sub-Total" = "O" by summing of all the single costs, until the next line of same level as current line

Here below, more complete data, to calculate

 BILL OF MATERIAL Line Line Above current Level of BoM Parent Article Current Article Type Sub-Total Line Cost Obective Sum 0 -1 0 CN0 Assy O 20,00 € 1 475,59 € =SUM(I4:I63) 1 0 1 CN0 CN1,1 Assy O 50,00 € 1 224,26 € =SUM(I5:I49) 2 1 2 CN1,1 CN2,01 Purchase 1,00 € 3 1 2 CN1,1 CN2,02 Purchase 0,01 € 4 1 2 CN1,1 CN2,03 Purchase 0,01 € 5 1 2 CN1,1 CN2,04 Purchase 0,01 € 6 1 2 CN1,1 CN2,05 Purchase 0,10 € 7 1 2 CN1,1 CN2,06 Purchase 0,01 € 8 1 2 CN1,1 CN2,07 Purchase 1,00 € 9 1 2 CN1,1 CN2,08 Purchase 0,10 € 10 1 2 CN1,1 CN2,09 Purchase 0,10 € 11 1 2 CN1,1 CN2,10 Assy O 5,00 € 155,00 € =SUM(I15:I18) 12 11 3 CN2,10 CN3,01 Assy O 20,00 € 70,00 € =SUM(I16:I17) 13 12 4 CN3,01 CN4,01 Assy 50,00 € 14 11 3 CN2,10 CN3,02 Purchase 80,00 € 15 1 2 CN1,1 CN2,11 Assy O 50,00 € 528,61 € =SUM(I19:I36) 16 15 3 CN2,11 CN3,03 Purchase 80,00 € 17 15 3 CN2,11 CN3,04 Purchase 0,10 € 18 15 3 CN2,11 CN3,05 Purchase 0,10 € 19 15 3 CN2,11 CN3,06 Purchase 0,01 € 20 15 3 CN2,11 CN3,07 Purchase 0,10 € 21 15 3 CN2,11 CN3,08 Purchase 0,10 € 22 15 3 CN2,11 CN3,09 Purchase 1,00 € 23 15 3 CN2,11 CN3,10 Purchase 0,10 € 24 15 3 CN2,11 CN3,11 Purchase 0,10 € 25 15 3 CN2,11 CN3,12 Purchase 50,00 € 26 15 3 CN2,11 CN3,13 Purchase 1,00 € 27 15 3 CN2,11 CN3,14 Purchase 10,00 € 28 15 3 CN2,11 CN3,15 Purchase 200,00 € 29 15 3 CN2,11 CN3,16 Purchase 100,00 € 30 15 3 CN2,11 CN3,17 Inter-site 20,00 € 31 15 3 CN2,11 CN3,18 Assy O 15,00 € 16,00 € =SUM(I35:I36) 32 31 4 CN3,18 CN4,02 Purchase 1,00 € 62 1 2 CN1,1 CN2,12 Assy O 60,00 € 60,10 € =SUM(I37:I38) 63 62 3 CN2,12 CN3,19 Purchase 0,10 € 64 1 2 CN1,1 CN2,13 Purchase 5,00 € 65 1 2 CN1,1 CN2,14 Purchase 1,00 € 66 1 2 CN1,1 CN2,15 Purchase 2,00 € 67 1 2 CN1,1 CN2,16 Assy O 40,00 € 405,11 € =SUM(I42:I46) 68 67 3 CN2,16 CN3,20 Purchase 350,00 € 69 67 3 CN2,16 CN3,21 Purchase 15,00 € 70 67 3 CN2,16 CN3,22 Purchase 0,01 € 71 67 3 CN2,16 CN3,23 Purchase 0,10 € 72 1 2 CN1,1 CN2,17 Purchase 10,00 € 73 1 2 CN1,1 CN2,18 Purchase 0,10 € 74 1 2 CN1,1 CN2,19 Purchase 5,00 € 75 0 1 CN0 CN1,2 Purchase 10,00 € 76 0 1 CN0 CN1,3 Purchase 5,00 € 77 0 1 CN0 CN1,4 Assy O 10,00 € 216,33 € =SUM(I52:I63) 78 77 2 CN1,4 CN2,20 Purchase 160,00 € 79 77 2 CN1,4 CN2,21 Purchase 0,10 € 80 77 2 CN1,4 CN2,22 Purchase 0,10 € 81 77 2 CN1,4 CN2,23 Purchase 0,01 € 82 77 2 CN1,4 CN2,24 Purchase 0,01 € 83 77 2 CN1,4 CN2,25 Purchase 0,01 € 84 77 2 CN1,4 CN2,26 Purchase 10,00 € 85 77 2 CN1,4 CN2,27 Purchase 20,00 € 86 77 2 CN1,4 CN2,28 Purchase 10,00 € 87 77 2 CN1,4 CN2,29 Purchase 6,00 € 88 77 2 CN1,4 CN2,30 Purchase 0,10 €

# Re: Sum a range of cells in column, as long as a condition is OK

@Sergei Baklan
I found a way.
Thanks to your proposal, it gave me the way.

# Re: Sum a range of cells in column, as long as a condition is OK

@Ace166 , glad to know you sorted this out, thank you for the update.

1 best response

Accepted Solutions
best response confirmed by Ace166 (Copper Contributor)
Solution

# Re: Sum a range of cells in column, as long as a condition is OK

Perhaps

if I understood the logic correctly.