Convert Evaluate("SUMPRODUCT") to WorksheetFunction

Question

How to convert this formula to application.worksheetfunction.sumproduct with assigning range?

Set wb2 = ThisWorkbook)
Set ws1 = wb2.Sheets("b")
Set ws17 = wb2.Sheets("c")
Set ws18 = wb.Sheets("a")
Set ws19 = wb.Sheets("d")

ws18.range("A:A")

Evaluate("SUMPRODUCT((A:A=J2)*(E:E=F:F))") + _
'Evaluate("SUMPRODUCT((A:A=J2)*(E:E>F:F)*(F:F>0))")

THANKS SO MUCH!

You can't because you can't compare a range to one value like that in VBA. What's wrong with `Evaluate`? — Rory, Jan 12 '15 at 16:25
Can you be more specific? As written your question makes very little sense to me, I'm afraid. — Rory, Jan 12 '15 at 16:42
this formula Evaluate("SUMPRODUCT((A:A=J2)(E:E=F:F))") + _ 'Evaluate("SUMPRODUCT((A:A=J2)(E:E>F:F)*(F:F>0))") will go something like this appplication.worksheetfunstion.evaluate(application.worksheetfunction.sumproduct(ws1.range(A:A)=ws17.range(j2))) — alkatraz, Jan 12 '15 at 16:45
I already said you can't do that. Why **specifically** can't you use Evaluate? — Rory, Jan 12 '15 at 17:00
Those full column references must throw that formula into a hissy fit if J2 is blank. — , Jan 12 '15 at 18:11
@Rory, Agree the question is unclear but IMHO it can sometimes be useful to convert formulas this way either for debugging purposes or for storing intermediate results and avoiding formula length limit. And although operators like `=` and `>=` can't be used this way, they can be replaced by worksheet function equivalents: `DELTA` and `GESTEP` as suggested below. — lori_m, Jan 14 '15 at 09:37
@lori_m Thanks - that's a very interesting approach! Not one I'd necessarily choose for clarity, but very useful to be aware of. :) — Rory, Jan 14 '15 at 10:13

score 0 · Accepted Answer · edited May 23 '17 at 10:24

0

Converting a formula to a vba worksheet function equivalent is often possible but can be tricky as only a limited subset of functions are available. (See also here: Adding or multiplying variants in VBA)

Based on the example given, i think the following two functions should return the same result:

Sub TestEvaluate()
With Sheet1
    x = .Evaluate("SUMPRODUCT((A1:A10=J2)*(E1:E10=F1:F10))")
    y = .Evaluate("SUMPRODUCT((A1:A10=J2)*(E1:E10>F1:F10)*(F1:F10>0))")
End With
Debug.Print x + y
End Sub

Sub TestWorksheetFunction()
With Sheet1
    a = .Range("A1:A10").Value2
    e = .Range("E1:E10").Value2
    f = .Range("F1:F10").Value2
    Set j = .Range("J2")
End With
With Application
    x = .SumProduct(.CountIf(j, a), .Delta(e, f))
    y = .SumProduct(.CountIf(j, a), .Delta(.GeStep(f, e)), .Delta(.GeStep(0, f)))
End With
Debug.Print x + y
End Sub

edited May 23 '17 at 10:24

Community

1
1

answered Jan 13 '15 at 10:01

lori_m

5,487
1
18
29

1

Probably worth clarifying that you are assuming that E and F are numeric? Also, SUMPRODUCT and COUNTIF don't always return the same result - eg with numbers stored as text, or long numeric codes. Nitpicking I know, but just for completeness! ;) – Rory Jan 14 '15 at 10:23
Indeed, thanks for clarifying that. The assumption that columns E and F contain numbers seems reasonable given the inequality in the question and COUNTIF does have some edge cases as you say - including wild cards. Goes to show why it's good practice to put example data with the question! Having said that, I think there is often a tacit understanding with formula solutions given in forums that they should work for standard type inputs but not necessarily with every conceivable test case. – lori_m Jan 14 '15 at 14:47

Convert Evaluate("SUMPRODUCT") to WorksheetFunction

1 Answers1