Sum of each S.No.on the top in a one attempt

[PRADEEPB270]

Pl.refer my attach file and looking the best excel solution for this.
In this file,I want the grand total ( sum ) of the amount ( Col.E) of my each S.No.on the top.
For an example,S.No.7,cell no.E28,that are the total of range E29:E40.All should be in one attempt.How?

[HansV]

Here is a macro to create the formulas:

Code: Select all

Sub CreateFormulas()
    Const ProductCol = "C"
    Const AmountCol = "D"
    Const FirstRow = 5
    Dim LastRow As Long
    Dim CurRow As Long
    Dim EndRow As Long
    Application.ScreenUpdating = False
    LastRow = Range(AmountCol & Rows.Count).End(xlUp).Row
    For CurRow = LastRow To FirstRow Step -1
        If Range(ProductCol & CurRow).Value = "" Then
            Range(AmountCol & CurRow).Formula = _
                "=SUM(" & AmountCol & (CurRow + 1) & ":" & AmountCol & EndRow & ")"
            EndRow = CurRow - 1
        End If
    Next CurRow
    Application.ScreenUpdating = True
End Sub

[PRADEEPB270]

Not working.Pl.see the attach file.

[HansV]

In the file that you originally attached, the description was in column C and the amount in column D. The constants at the beginning of the macro are for that situation.
In the new workbook, the description is in column D and the amount in column E. However, you only changed AmountCol and not ProductCol. You should have

Code: Select all

    Const ProductCol = "D"

[PRADEEPB270]

Sorry for my mistake.
Pl.refer the attach file.All is fine except cell no.E41.Why the formula is not coming?

[HansV]

Sorry about that - I forgot one line:

Code: Select all

Sub CreateFormulas()
    Const ProductCol = "D"
    Const AmountCol = "E"
    Const FirstRow = 5
    Dim LastRow As Long
    Dim CurRow As Long
    Dim EndRow As Long
    Application.ScreenUpdating = False
    LastRow = Range(AmountCol & Rows.Count).End(xlUp).Row
    EndRow = LastRow ' *** new line ***
    For CurRow = LastRow To FirstRow Step -1
        If Range(ProductCol & CurRow).Value = "" Then
            Range(AmountCol & CurRow).Formula = _
                "=SUM(" & AmountCol & (CurRow + 1) & ":" & AmountCol & EndRow & ")"
            EndRow = CurRow - 1
        End If
    Next CurRow
    Application.ScreenUpdating = True
End Sub

[PRADEEPB270]

Thanks Hans for your nice cooperation.I oblige.