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

[BURKMANTSU]

A	B	C	D	E	F	G
Destination	P/V	Pup	Van	Dest. 2	Pup Total	Van Total
003	p	1		001
003	v		1	002
004	v		1	003
004	p	1		004
005	p	1		005
009	p	1		006
012	v		1	007
030	v		1	008

<tbody>
</tbody>

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.