Search code examples
vbaexcelsumifs

Excel VBA code for SumIF function

Hi I am writing VBA code such that -

If the value of cell M1 is found in any cell Value of Range D1:D20 then Find the sum of corresponding cell values in Range C1:C20. But I am getting confused as to how to use the function and pass the parameters. This is what I have tried now, but it does not work

Private Sub FindTotal()
    Dim Temp2Sheet As Worksheet

    Set Temp2Sheet = Sheets("Temp2")
    Temp2Sheet.Activate

    Dim temp As String
    temp = Temp2Sheet.Range("M1").Value
    Temp2Sheet.Range("N1").Value = Application.SumIf(Range("D1:D20"), "" * "&temp&" * "", Range("C1:C20"))

End Sub

I need to extend same logic for Range M1 to how many ever values are there is Column M and it is dynamic. The totals will appear in Column N in the corresponding cell of column M. Like M1 total will be in N1, and so on

My main confusion here is how do I pass the temp variable as parameter 2 of SUMIF function and add wild cards (**)

Any help is appreciated. Thanks in advance

Solution

  • This is what I mean by triple quotes:

    SumIf(Range("D1:D20"), """*" & temp & "*""", Range("C1:C20"))