Excel VBA code for SumIF function

vba excel sumifs
ravi picture ravi · May 13, 2014 · Viewed 82.4k times · Source

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

Answer

Tim Williams picture Tim Williams · May 13, 2014

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

Related questions

Sumifs in Excel-VBA

I have some problem with sumifs in vba: Dim Arg1 As Range 'the range i want to sum Dim Arg2 As Range 'criteria range Dim Arg3 As Variant 'the criteria Set Arg1 = ThisWB.Sheets("Sheet1").Range("B2:B100") Set Arg2 = …

Read More
Is there a way to crack the password on an Excel VBA Project?

I've been asked to update some Excel 2003 macros, but the VBA projects are password protected, and it seems there's a lack of documentation... no-one knows the passwords. Is there a way of removing or cracking the password on a VBA …

Read More
How do I declare a global variable in VBA?

I wrote the following code: Function find_results_idle() Public iRaw As Integer Public iColumn As Integer iRaw = 1 iColumn = 1 And I get the error message: "invalid attribute in Sub or Function" Do you know what I did wrong? I tried …

Read More