What is the sumproduct syntax in vba?

Question

What is the correct syntax for sum product?

Here is my code:

mFormula = "=SUMPRODUCT(--((1.Insert_Data!F:F)=2.Counting_sheet!A8)),(--((1.Insert_Data!E:E)))"
Range("C8") = Evaluate(mFormula)

What makes you think that's incorrect? In other words, what happens when you run that? — Tim Williams, Nov 01 '20 at 21:09
Why don't you use `Range("C8").Formula = "=SUMPRODUCT(--((1.Insert_Data!F:F)=2.Counting_sheet!A8)),(--((1.Insert_Data!E:E)))"`? It should work — Rafael Américo Sartori, Nov 01 '20 at 21:29

Tim Williams · Answer 1 · 2020-11-01T22:15:45.550

1

Your parentheses are not balanced

mFormula = "=SUMPRODUCT(  --(('1.Insert_Data'!F:F)='2.Counting_Sheet'!A8), " & _
                         "--('1.Insert_Data'!E:E)  )"

Range("C8") = Evaluate(mFormula)

edited Nov 01 '20 at 22:15

answered Nov 01 '20 at 21:37

Tim Williams

154,628
8
97
125

If you use the same formula in a worksheet cell does it work? Is the workbook containing those sheets active? – Tim Williams Nov 02 '20 at 02:06
Dim e As Integer Dim v As Integer Dim mFormula As String For e = 8 To 120 mFormula = "=SUMPRODUCT( --(('1.Insert_Data'!F:F) = '2.Counting_sheet'!A&e), " & _ "'1.Insert_Data'!E:E)" Range("C" & e) = Evaluate(mFormula) Next e Still did not work instead gave me a name error – Vincent Vuong Nov 03 '20 at 04:17

score 0 · Answer 2 · answered Nov 03 '20 at 04:16

0

mFormula = "=SUMPRODUCT(  --(('1.Insert_Data'!F:F) = '2.Counting_sheet'!A8), " & _
                     "'1.Insert_Data'!E:E)"

this is the correct syntax

answered Nov 03 '20 at 04:16

Vincent Vuong

27
2

score 0 · Answer 3 · answered Nov 03 '20 at 04:40

Sub itemno() '

' Dim e As Integer Dim mFormula As String

For e = 8 To 120

mFormula = "=SUMPRODUCT(  --(('1.Insert_Data'!F:F) = '2.Counting_sheet'!A" & e & ")," & _
                     "'1.Insert_Data'!E:E)"
                     
                     
Range("C" & e).Value = mFormula

Next e

End Sub

What is the sumproduct syntax in vba?

3 Answers3