Sum Column C by Individual Destinations in Column A & Place Sum Total in matching Individual Destination in Column E

[BURKMANTSU]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Destination[/TD]
[TD]P/V[/TD]
[TD]Pup[/TD]
[TD]Van[/TD]
[TD]Dest. 2[/TD]
[TD]Pup Total[/TD]
[TD]Van Total[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]p[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]001[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]v[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]v[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]003[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]p[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]004[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]p[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]005[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]009[/TD]
[TD]p[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]006[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]012[/TD]
[TD]v[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]007[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]030[/TD]
[TD]v[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]008[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


All Columns go to 500. Column A for Destinations is not set and can change depending on the data entered. Column E is a list of all Destinations. Both Columns A & E have a range of 001 to 498.

I want to sum Column C for each Destination in Column A and place these totals into Column F into the correct Destination. I will also want to do this for Column G as well.

I just want to know the total number of pups & vans from Column A and have those totals shown in Columns F & G.

I appreciate any advice/suggestions.

 

[Marcelo Branco]

Welcome to Mr Excel forum

Try
Formula in F2 copied across and down
=SUMIF($A$2:$A$500,$E2,C$2:C$500)

Hope this helps

M.

 

[mumps]

Try:

Sub sumDest()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rngUniques As Range, dest As Range, foundDestRow As Long
    Sheets("Sheet1").Range("A1:A" & LastRow).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range _
        ("A1:A" & LastRow), Unique:=True
    Set rngUniques = Sheets("Sheet1").Range("A2:A" & LastRow).SpecialCells(xlCellTypeVisible)
    If Sheets("Sheet1").FilterMode Then Sheets("Sheet1").ShowAllData
    For Each dest In rngUniques
        foundDestRow = Range("E:E").Find(dest).Row
        With Cells(1, 1).CurrentRegion
            .AutoFilter 1, dest
            Cells(foundDestRow, "F") = WorksheetFunction.Sum(Range("C2:C" & LastRow).SpecialCells(xlCellTypeVisible))
            Cells(foundDestRow, "G") = WorksheetFunction.Sum(Range("D2:D" & LastRow).SpecialCells(xlCellTypeVisible))
            .AutoFilter
        End With
    Next dest
    Application.ScreenUpdating = True
End Sub

 

[BURKMANTSU]

Yes, thank you! At first, each column showed zeros for the sum. Column C & D are "if" formulas based on Column B. I inserted a Column in between C & D, used =INT(c2) in column D, updated the formula and all of a sudden the sum totals worked. Must be something with the formatting with the "if" formula.

Again, thank you for the formula for the sums!!!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.