Forum Discussion
Solved
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 ...
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 € |
Ace166 , glad to know you sorted this out, thank you for the update.