Excel VBA Sum if

[Bwachtel]

What i am trying to do is i have two sheets. One with the raw data and the second sheet with a summary. I want to Compare Range(A2 to A19) on sheet two to range (A2 to A300) On sheet one and if they match, then sum Range (B2 to B300) on Sheet one and put the final summed number in cell B21 on sheet two. Here is the code i am doing right now. The comments at the end are a couple attempts i had at doing it without the sumif function. So far it worksish, but only by finding the first match and then it stops.

Also, the rest of the code is working fine. I just pasted the whole thing just in case for some reason the other code is messing with it. The intent of the other code is to copy two dynamic ranges from Sheet one to Sheet two, Sort them, then delete the duplicates.

Greatly appreciate any help!

Sub sortList2()

    'Defining Variables
    Dim CssLength As Integer
    Dim DesignPackageLength As Integer
    Dim NewCssLength As Integer
    Dim NewDesignPackageLength As Integer
    Dim i As Integer
    
    'Finding the total row length for packages and CSS's
    CssLength = Sheets("Project List").Cells(Rows.count, 10).End(xlUp).Row
    DesignPackageLength = Sheets("Project List").Cells(Rows.count, 4).End(xlUp).Row
    
    'Clearing the old contents of the Total kVA sheet
    Sheets("Total kVA").Range("A2:A" & Sheets("Total kVA").Cells(Rows.count, 1).End(xlUp).Row).ClearContents
    Sheets("Total kVA").Range("B2:B" & Sheets("Total kVA").Cells(Rows.count, 2).End(xlUp).Row).ClearContents
    Sheets("Total kVA").Range("D2:D" & Sheets("Total kVA").Cells(Rows.count, 4).End(xlUp).Row).ClearContents
    Sheets("Total kVA").Range("E2:E" & Sheets("Total kVA").Cells(Rows.count, 5).End(xlUp).Row).ClearContents
    
    'Copying the Project List to the Total kVA sheet
    Sheets("Project List").Range("J2:J" & CssLength).Copy
    Sheets("Total kVA").Range("D2:D" & CssLength).PasteSpecial Paste:=xlPasteValues
    
    'Sorts the list to ascending order
    Sheets("Total kVA").Range("D1:D" & CssLength).Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlYes
    'Removed the duplicates from the list
    Sheets("Total kVA").Range("D1:D" & CssLength).RemoveDuplicates Columns:=1, Header:=xlYes
    'Add's the deleted borders back
    Sheets("Total kVA").Range("D1:D" & CssLength).Borders.LineStyle = xlContinuous
    
    'Copying the CSS list to the Total kVA sheet
    Sheets("Project List").Range("D2:D" & CssLength).Copy
    Sheets("Total kVA").Range("A2:A" & CssLength).PasteSpecial Paste:=xlPasteValues
    
    'Sorts the list to ascending order
    Sheets("Total kVA").Range("A1:A" & DesignPackageLength).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    'Removed the duplicates from the list
    Sheets("Total kVA").Range("A1:A" & DesignPackageLength).RemoveDuplicates Columns:=1, Header:=xlYes
    'Add's the deleted borders back
    Sheets("Total kVA").Range("A1:A" & DesignPackageLength).Borders.LineStyle = xlContinuous
    
    'Finding the total row length for new packages and CSS's lists on sheet Total kVA
    NewCssLength = Sheets("Total kVA").Cells(Rows.count, 4).End(xlUp).Row
    NewDesignPackageLength = Sheets("Total kVA").Cells(Rows.count, 1).End(xlUp).Row
        
    'Addign the total fields at the end of each column.
    Sheets("Total kVA").Range("D" & NewCssLength + 2).Value = "Total kVA"
    Sheets("Total kVA").Range("A" & NewDesignPackageLength + 2).Value = "Total kVA"
    
    Sheets("Total kVA").Range("B2").Value = Application.SumIf(Sheets("Total kVA").Range("A2:A" & NewDesignPackageLength), Sheets("Project List").Range("D2:D" & DesignPackageLength), Sheets("Project List").Range("Aj2:Aj" & NewDesignPackageLength))
    
    'For i = 2 To DesignPackageLength
     '   If Sheets("Total kVA").Range("A2").Value = Sheets("Project List").Range("D" & i).Value Then
      '      Sheets("Total kVA").Range("B2").Value = Sheets("Total kVA").Range("B2").Val + Sheets("Package List").Range("AJ" & i)
       ' End If
    'Next i
    
    'For i = 2 To DesignPackageLength
    '    If Sheets("Total kVA").Cell(2, 1).Value = Sheets("Project List").Cell(i, 4).Value Then
    '        Sheets("Total kVA").Cell(2, 2) = Sheets("Total kVA").Cell(2, 2) + Sheets("Package List").Cell(i, 36)
    '    End If
    'Next i
    


End Sub

 

[Bwachtel]

Ive Modified it to this so far.

    Sheets("Total kVA").Range("B2").Value = Application.SumIf(Sheets("Project List").Range("D2:D" & DesignPackageLength), Sheets("Total kVA").Range("A2").Value, Sheets("Project List").Range("Aj2:Aj" & DesignPackageLength))

 

[Bwachtel]

Well it appears to be working now? not sure what i actually changed to fix it but not going to complain lol. Here is the final code in case someone else stumbles across this.

For i = 2 To NewDesignPackageLength        
Sheets("Total kVA").Range("B" & i).Value = Application.SumIf(Sheets("Project List").Range("D2:D" & DesignPackageLength), Sheets("Total kVA").Range("A" & i).Value, Sheets("Project List").Range("Aj2:Aj" & DesignPackageLength))
    Next i