Forum Discussion
Ace166
Jan 03, 2023Copper 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.
- Ace166Copper Contributor
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 € - Ace166Copper Contributor