calculate receipe bom cost

i want to calculate the receipe bom cost with excel function with the help of data in the column D & F named as S.NO & RECEIPE NAME.

ColumnD named as SNO represents the receipe category which is further divided into sub-category of main receipe category, while column F named as RECEIPE NAME is item description.
ColumnE contains seperate items which comes together to form RECEIPE.

for detail please see the attach file.


Regards,
Deepak

Hi Depak -Please explain your concept how you wish to achieve your desired result and what are criteria and final result value.

Are you still using Excel 2007 ? If not, please update your profile with current version.

You did not counted the expected result manualy in your file, so I could not check the result.


With a Pivot Table.

Results are explained:

G4 = sum(G5:G7)

i.e A (G4) =sum(B:D) where A, B ... etc refer to letters in column E

So that is what i am asking how your conditions are building ? How G4= Sum(G5:G7)??

The summation is based on "S.No"

Level 1 (or first level) is sum of single number levels 1,2,3: Level 3 is the sum of 3.1 and 3.2 and so on.

@depak: See post #3 and please reply to it.

@depak.bish

Please also reply on #4.

Sub BOM_Sum()
Dim rng As Range
Dim ar

Application.ScreenUpdating = False

lr = Cells(Rows.Count, "D").End(xlUp).Row
Set rng = Range("D1:H" & lr)
srow = 4

For r = 1 To lr  ' Loop through data

    rname = rng(srow, 3)   ' Recipe Name

    n = Application.WorksheetFunction.CountIf(rng, rname)  ' Count rows for is Recipe
    
    nrow = n
    lrow = srow + n - 1
    
    ar = Range("D" & srow & ":G" & lrow)  ' assign data to work array
       
    ar(1, 4) = 0   ' Set total to zero
    
    Do While nrow >= 2  ' Loop through array ( last to first)
    
        sNo = Len(ar(nrow, 1))  ' length of S.No
        
        Select Case sNo
        
            Case Is = 1  ' single S.No (1,2 3 etc)
                first = True
                For i = 2 To n
                    If InStr(1, ar(i, 1), ar(nrow, 1)) > 0 And i <> nrow And Left(ar(i, 1), 1) = ar(nrow, 1) Then
                       If first Then
                           ar(nrow, 4) = ar(i, 4)
                           first = False
                       Else
                           ar(nrow, 4) = ar(nrow, 4) + ar(i, 4)
                       End If
                   End If
                Next i
                
                ar(1, 4) = ar(1, 4) + ar(nrow, 4)
                
            Case Is = 3 'sno of form "x.y"
            
                first = True
                For i = 2 To n
                    If InStr(1, ar(i, 1), ar(nrow, 1)) > 0 And i <> nrow Then ' match to "x.y.z"
                       If first Then
                           ar(nrow, 4) = ar(i, 4)
                           first = False
                       Else
                           ar(nrow, 4) = ar(nrow, 4) + ar(i, 4)
                       End If
                     End If
                 Next i
            
        End Select
        
        nrow = nrow - 1
    Loop
    
    Range("K" & srow).Resize(UBound(ar, 1), UBound(ar, 2)) = ar  'output results
    
    srow = lrow + 2
    r = srow - 1
    
Next r

Application.ScreenUpdating = True

End Sub

in Sheet2 ....

Helper columns in I & j

in I4

=--SUBSTITUTE(D4,".","")

in J4

=INT($I4/100)

in K4

Formula:

=IF(COUNTIF($F$4:$F4,$F4)=1,IF(J4=0,SUMIFS($G5:$G$26,$J5:$J$26,0,$F5:$F$26,F5)),IFERROR(IF(MATCH(I5,J4:$J$26,0),SUMIFS(G5:$G$26,J5:$J$26,I5,F4:$F$26,F5)),G4))