CODEWITHSUNDEEP
vbaexcelsumifs
ravi
ravi

Reputation: 1847

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

Upvotes: 2

Views: 86984

Answers (3)

Manivannan KG
Manivannan KG

Reputation: 371

For looping concepts, Tim's code can be modified as

Dim rowIndex As Long
Dim sourceWorksheet As Worksheet

For rowIndex = 2 To lastRow
 calcFormula = Application.SumIf(WorkSheet.Range("B:B"), _
                          "*" & Worksheet.Cells(rowIndex, "Q").Value & "*", _
                        WorkSheet.Range("L:L"))
        Worksheet.Cells(rowIndex, "AS").Value = calcFormula  ' output column
Next rowIndex

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166885

This should do what you want, unless you're looking for something more than just finding basic substrings:

Private Sub FindTotal()

    With Sheets("Temp2")
        .Range("N1").Value = Application.SumIf(.Range("D1:D20"), _
                              "*" & .Range("M1").Value & "*", _
                            .Range("C1:C20"))
    End With

End Sub

Upvotes: 6

Michael Blaustein
Michael Blaustein

Reputation: 901

This is what I mean by triple quotes:

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

Upvotes: 2

Related Questions