SOLVED

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

Copper Contributor

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 #LevelValueSum (objective)Formula (Sum of values) explanation
80111=sum(Line8:Line18)
9115=sum(Line9:Line13)
10212=sum(Line10:Line11)
1131  
12212=sum(Line12:Line13)
1331  
14115=sum(Line14:Line18)
15212=sum(Line15:Line16)
1631  
17212=sum(Line17:Line18)
1831  

I hope this is clear enough.

Thank you in advance for you help.

 

 

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

@Ace166 

Perhaps

image.png

if I understood the logic correctly. 

@Sergei Baklan 

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)

 

 

 ABCDEFGHIJKL
1 Explanation of the Bill of Material table       
2            
3 LineLine Above currentLevel of BoMParent ArticleCurrent ArticleTypeSub-TotalLine Cost Obective Sum 
4 0-10 CN0AssyO20,00 € 1 475,59 € 
5 101CN0CN1,1AssyO50,00 € 52,14 € 
6 212CN1,1CN2,01Purchase 1,00 €   
7 312CN1,1CN2,02Purchase 0,01 €   
8 412CN1,1CN2,03Purchase 0,01 €   
9 512CN1,1CN2,04Purchase 0,01 €   
10 612CN1,1CN2,05Purchase 0,10 €   
11 712CN1,1CN2,06Purchase 0,01 €   
12 812CN1,1CN2,07Purchase 1,00 €   
13            
14            
15Col. BLine = Number of line / Line number in the BoM       
16Col. CLine Above Current = Line of which the current line is depending      
17Col. D Level of BoM = Level in the descending "tree" of the BoM      
18Col. EParent Article = Article number above the current Article       
19Col. FCurrent Article = Article(s) depending of a (specific) parent article      
20Col. GType ==> 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
21Col. HSub-Total = if "O" ==> lines for which we need to calculate the sub-total cost (for all levels above the current one)   
22Col. I Actual current line cost         

23

Col. KManual 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        
           
LineLine Above currentLevel of BoMParent ArticleCurrent ArticleTypeSub-TotalLine Cost Obective Sum 
0-10 CN0AssyO20,00 € 1 475,59 €=SUM(I4:I63)
101CN0CN1,1AssyO50,00 € 1 224,26 €=SUM(I5:I49)
212CN1,1CN2,01Purchase 1,00 €   
312CN1,1CN2,02Purchase 0,01 €   
412CN1,1CN2,03Purchase 0,01 €   
512CN1,1CN2,04Purchase 0,01 €   
612CN1,1CN2,05Purchase 0,10 €   
712CN1,1CN2,06Purchase 0,01 €   
812CN1,1CN2,07Purchase 1,00 €   
912CN1,1CN2,08Purchase 0,10 €   
1012CN1,1CN2,09Purchase 0,10 €   
1112CN1,1CN2,10AssyO5,00 € 155,00 €=SUM(I15:I18)
12113CN2,10CN3,01AssyO20,00 € 70,00 €=SUM(I16:I17)
13124CN3,01CN4,01Assy 50,00 €   
14113CN2,10CN3,02Purchase 80,00 €   
1512CN1,1CN2,11AssyO50,00 € 528,61 €=SUM(I19:I36)
16153CN2,11CN3,03Purchase 80,00 €   
17153CN2,11CN3,04Purchase 0,10 €   
18153CN2,11CN3,05Purchase 0,10 €   
19153CN2,11CN3,06Purchase 0,01 €   
20153CN2,11CN3,07Purchase 0,10 €   
21153CN2,11CN3,08Purchase 0,10 €   
22153CN2,11CN3,09Purchase 1,00 €   
23153CN2,11CN3,10Purchase 0,10 €   
24153CN2,11CN3,11Purchase 0,10 €   
25153CN2,11CN3,12Purchase 50,00 €   
26153CN2,11CN3,13Purchase 1,00 €   
27153CN2,11CN3,14Purchase 10,00 €   
28153CN2,11CN3,15Purchase 200,00 €   
29153CN2,11CN3,16Purchase 100,00 €   
30153CN2,11CN3,17Inter-site 20,00 €   
31153CN2,11CN3,18AssyO15,00 € 16,00 €=SUM(I35:I36)
32314CN3,18CN4,02Purchase 1,00 €   
6212CN1,1CN2,12AssyO60,00 € 60,10 €=SUM(I37:I38)
63623CN2,12CN3,19Purchase 0,10 €   
6412CN1,1CN2,13Purchase 5,00 €   
6512CN1,1CN2,14Purchase 1,00 €   
6612CN1,1CN2,15Purchase 2,00 €   
6712CN1,1CN2,16AssyO40,00 € 405,11 €=SUM(I42:I46)
68673CN2,16CN3,20Purchase 350,00 €   
69673CN2,16CN3,21Purchase 15,00 €   
70673CN2,16CN3,22Purchase 0,01 €   
71673CN2,16CN3,23Purchase 0,10 €   
7212CN1,1CN2,17Purchase 10,00 €   
7312CN1,1CN2,18Purchase 0,10 €   
7412CN1,1CN2,19Purchase 5,00 €   
7501CN0CN1,2Purchase 10,00 €   
7601CN0CN1,3Purchase 5,00 €   
7701CN0CN1,4AssyO10,00 € 216,33 €=SUM(I52:I63)
78772CN1,4CN2,20Purchase 160,00 €   
79772CN1,4CN2,21Purchase 0,10 €   
80772CN1,4CN2,22Purchase 0,10 €   
81772CN1,4CN2,23Purchase 0,01 €   
82772CN1,4CN2,24Purchase 0,01 €   
83772CN1,4CN2,25Purchase 0,01 €   
84772CN1,4CN2,26Purchase 10,00 €   
85772CN1,4CN2,27Purchase 20,00 €   
86772CN1,4CN2,28Purchase 10,00 €   
87772CN1,4CN2,29Purchase 6,00 €   
88772CN1,4CN2,30Purchase 0,10 €   

 

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

@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

@Ace166 

Perhaps

image.png

if I understood the logic correctly. 

View solution in original post