Forum Discussion

Ace166's avatar
Ace166
Copper Contributor
Jan 03, 2023

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 #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.

 

 

    • Ace166's avatar
      Ace166
      Copper Contributor

      SergeiBaklan 

      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 €   

       

      • Ace166's avatar
        Ace166
        Copper Contributor
        SergeiBaklan 
        I found a way.
        Thanks to your proposal, it gave me the way.
        Thank you for your help.

Resources