Worksheetfunction.sumif returning string

Question

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.

in place of & " , -" & and ` " , +" &` which is forcing it into a string put - and + respectively, without any wrapper function. — Scott Craner
– Scott Craner, Commented Jun 17, 2016 at 20:44
Hi Scott, thanks for editing, I tried doing this and I get like 80-1000000, it does not appear to calculate the applicationsumifs — Lowpar
– Lowpar, Commented Jun 17, 2016 at 20:49
is temp a variable or are you looking for anything that contains the word "temp"? — Scott Craner
– Scott Craner, Commented Jun 17, 2016 at 20:53
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")) — Scott Craner
– Scott Craner, Commented Jun 17, 2016 at 20:55
Hi Scott, temp is a variable containing the string I am looking for, the sumif is definitely working correctly, because it is returning the correct values, however it is not calculating the values. — Lowpar
– Lowpar, Commented Jun 17, 2016 at 20:58

Scott Craner · Accepted Answer · 2016-06-17 21:38:44Z

1

So I set up my sheet like this:

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

Which when double checking manually, is the correct answer:

Unless I am missing something.

edited Jun 17, 2016 at 21:38

answered Jun 17, 2016 at 21:23

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lowpar Over a year ago

Thanks Scott, would you believe I think I forgot to put the comma after the sumifs, omg I feel like an idiot, I am sure this will work! I will let you know on Monday, but until then, I will give you a preemptive thumbs up! Thank you for the help, and sorry for being so neglectful with my syntax.

Collectives™ on Stack Overflow

Worksheetfunction.sumif returning string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related