Sum amounts based on another column

[jaydee]

Hi there,

Sorry to bother everyone, but I am trying to sum the amounts in col L, based on each change in col C. (i.e for every change in PO #, I would like the amounts summed and placed in the column next to it). I am trying to produce the highlighted values in yellow in column M without having to do it manually.

I know the subtotal function produces the totals, however it is located in the same column as the amounts making it hard to read and I have to use the subgroup feature to see the changes, but sometimes need to see all of the data

Can someone help if it's not too hard to write a macro?

Thank you

[Teeroy]

Hi Jaydee,

Try:

[vba]Sub instant_sum()
Dim rStart As Range, rCurrent As Range
Dim lastrow As Long
With Sheets("Recon 1501")
Set rStart = .Range("C4")
lastrow = .Range("C" & Rows.Count).End(xlUp).Row
Set rCurrent = rStart
Do
If rCurrent.Offset(1, 0).Value <> rCurrent.Value Then
Range("M" & rCurrent.Row).Formula = "=Sum(L" & rStart.Row & ":L" & rCurrent.Row & ")"
Set rStart = rCurrent.Offset(1, 0)
End If

Set rCurrent = rCurrent.Offset(1, 0)
Loop While rCurrent.Row <= lastrow
End With
End Sub
[/vba]

[jaydee]

Thanks Teeroy, works great! I appreciate your help

[AMontes]

Formula option in M4:
=IF(C4=C5,"",SUMIF(C$4:C$68,C4,L$4:L$68))
Fill down

[Bob Phillips]

[VBA]Public Sub AddSumFormula
Dim lastrow As Long

With Worksheets("Recon 1501")

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("M4").Resize(lastrow - 3).Formula = "=IF(C4=C5,"""",SUMIF($C:$C,$C4,$L:$L))"
End With
End Sub[/VBA]

[ekayp]

Hi,
This example is similar to what I've been scratching my head to do.So thanks to all contributors.
Is there a same way I can add the count in another column without having to manually insert them in for hundred of records? I'm hoping to then do add an average in another column.
Any help much apprecitaed.
Thanks in advance.