CODEWITHSUNDEEP
vbaexcel
Lowpar
Lowpar

Reputation: 907

Worksheetfunction.sumif returning string

I have multiple worksheetfunction.sumifs in vba and they are calculating correctly, however when I try and stand the 

worksheetfunction.sumifs val = Application.SumIf(Range("D1:D20"), "" *          "&temp&" * "", Range("C1:C20")) & " , -" & Application.SumIf(Range("E1:E20"), "" * "&temp&" * "", Range("H1:H20")) & " , +" & Application.SumIf(Range("F1:F20"), "" * "&temp&" * "", Range("I1:I20")) & " , -" & Application.SumIf(Range("G1:G20"), "" * "&temp&" * "", Range("J1:J20"))

the variable is returned as a string, with the calculation, something like "80-100-0-0-0-0". I would like the worksheetfunction.sumif to actually sum the product of the sumifs. I have tried putting worksheetfunction.sum around the sumifs but to no avail. Any ideas? The reason I am doing this is to not have formulas in a different cell to speed up the very large Excel I am working with. I have also tried doing the sumifs with the ", -" but no joy either.

Upvotes: 0

Views: 341

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

So I set up my sheet like this:

enter image description here

Then I ran this code:

Sub show()
Dim temp As String
Dim val As Double
temp = "test"
val = Application.SumIf(Range("D1:D20"), "* " & temp & " *", Range("C1:C20")) - Application.SumIf(Range("E1:E20"), "* " & temp & " *", Range("H1:H20")) _
    + Application.SumIf(Range("F1:F20"), "* " & temp & " *", Range("I1:I20")) - Application.SumIf(Range("G1:G20"), "* " & temp & " *", Range("J1:J20"))
Debug.Print val
End Sub

The result was 11

enter image description here

Which when double checking manually, is the correct answer:

enter image description here

Unless I am missing something.

Upvotes: 1

Related Questions