sumproduct vba

[Vanda_a]

Hello all

I am new to VBA, I am trying to learn it.

First thing I have tried was fail, please kindly advise

Sub Learning
Cells(6, 11) = Application.WorksheetFunction.Sumproduct((Sheet2.Range("A2:A10") = Range("B6")) * (Sheet2.Range("B1") = Range("K5")) * Sheet2.Range("B2:B10"))
End Sub

Thank you in advance

 

[Norie]

Can you post the formula as it would look on a worksheet?

 

[Jonmo1]

Sumproduct in VBA doesn't work the same as it does in a formula.
It can't do the (range=criteria) expressions to creaate arrays.

Plus your ranges aren't the same size anyway
What is the intention of (Sheet2.Range("B1") = Range("K5")) ?

Do you have a working sumproduct formula in a Cell Formula?
Can you post it, and we can try to make it work in VBA.

 

[Special-K99]

Sumproduct is a worksheet function and not VBA according to internet posts.

 

[Vanda_a]

Can you post the formula as it would look on a worksheet?

Here it is

=SUMPRODUCT((Sheet2!$A$2:$A$10=Sheet1!B6)*(Sheet2!B1=Sheet1!K5)*Sheet2!$B$2:$B$10)

Thank you

 

[Vanda_a]

What is the intention of (Sheet2.Range("B1") = Range("K5")) ?

Can you post it, and we can try to make it work in VBA.

Sheet2 B1 = sheet1 K5.

Here it is

=SUMPRODUCT((Sheet2!$A$2:$A$10=Sheet1!B6)*(Sheet2!B1=Sheet1!K5)*Sheet2!$B$2:$B$10)

Thank you

 

[Jonmo1]

Try

Cells(6, 11) = Evaluate("=SUMPRODUCT((Sheet2!$A$2:$A$10=Sheet1!B6)*(Sheet2!B1=Sheet1!K5)*Sheet2!$B$2:$B$10)")

 

[Vanda_a]

Try

Cells(6, 11) = Evaluate("=SUMPRODUCT((Sheet2!$A$2:$A$10=Sheet1!B6)*(Sheet2!B1=Sheet1!K5)*Sheet2!$B$2:$B$10)")

Yeah this Evaluate is cool

Thank you very much. But may I know what problem with my code above? I must use this Evaluate?

I got more workbooks to sum so may i learn more beside Evaluate?

Cells(6, 11) = Application.WorksheetFunction.Sumproduct((C:\Users\Vanda_a\Desktop\Book1.xls.Sheet2.Range("A2:A10") = Range("B6")) * (C:\Users\Vanda_a\Desktop\Book1.xls.Sheet2.Range("B1") = Range("K5")) * C:\Users\Vanda_a\Desktop\Book1.xls.Sheet2.Range("B2:B10"))

 

[Jonmo1]

There's nothing 'wrong' with the code you tried.

It's just that sumproduct doesn't work in VBA the same way it does when used in a Cell Formula.
It can't process an array of criteria like (Sheet2!$A$2:$A$10=Sheet1!B6)

It's just the way it is with Sumproduct and VBA.
Evaluate seems to be the only way around it.

 

[RoryA]

It's not really related to SUMPRODUCT - it's that you can't create an array in VBA using a line like:

Sheet2.Range("A2:A10") = Range("B6")