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

 

[Vanda_a]

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")

Understand now.
Even this still no use?

Dim Rng as Range, Rng1 As Range
Set Rng = Range("B6")
Set Rng1 = Range("K5")
Cells(6, 11) = Application.WorksheetFunction.SumProduct((Sheet2.Range("A2:A10") = Rng) * (Sheet2.Range("B1") = Rng1) * Sheet2.Range("B2:B10"))

Thank you

 

[Vanda_a]

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.

I see. thank you very much

 

[RoryA]

Understand now.
Even this still no use?

No, you simply can't use range = something to create an array in VBA. For this example, you could use something like this:

Sub Learning()
    If Sheet2.Range("B1") = Range("K5") Then
        Cells(6, 11) = Application.WorksheetFunction.SumProduct(Application.CountIf(Range("B6"), Sheet2.Range("A2:A10")), Sheet2.Range("B2:B10"))
    Else
        Cells(6, 11) = 0
    End If
End Sub

 

[Vanda_a]

No, you simply can't use range = something to create an array in VBA. For this example, you could use something like this:

Sub Learning()
    If Sheet2.Range("B1") = Range("K5") Then
        Cells(6, 11) = Application.WorksheetFunction.SumProduct(Application.CountIf(Range("B6"), Sheet2.Range("A2:A10")), Sheet2.Range("B2:B10"))
    Else
        Cells(6, 11) = 0
    End If
End Sub

May I have more question later pls.

I try to understand it first.

Thank you very much

 

[Vanda_a]

Hi again

I dont understand this one

CountIf(Range("B6"), Sheet2.Range("A2:A10")

it should be CountIf(Sheet2.Range("A2:A10)", Range("B6")
Countif(Range, Criteria)

 

[RoryA]

If you do it that way round, you will only get one result. Doing it the way round I posted will treat each cell in A2:A10 in turn as the criterion for the COUNTIF and return an array of results, which is what you want.

 

[Vanda_a]

If you do it that way round, you will only get one result. Doing it the way round I posted will treat each cell in A2:A10 in turn as the criterion for the COUNTIF and return an array of results, which is what you want.

yes the result is what I want. Just double we need to flip range with criteria to get the correct result. Formula Countif(range, criteria) but in VBA countif(criteria, range).

thanks again

 

[RoryA]

Not exactly. It's still COUNTIF(range, criteria). We've just reversed the logic because of what we're trying to achieve. If you use:

CountIf(Sheet2.Range("A2:A10"), Range("B6"))

you will get a single result - the number of times the value in B6 occurs in A2:A10 on sheet 2.

That's not what we need for our SUMPRODUCT function - we need an array. So we reverse the construction:

CountIf(Range("B6"),Sheet2.Range("A2:A10"))

which actually equates to an array of 9 COUNTIF formulas:

CountIf(Range("B6"),Sheet2.Range("A2"))
CountIf(Range("B6"),Sheet2.Range("A3"))
CountIf(Range("B6"),Sheet2.Range("A4"))
CountIf(Range("B6"),Sheet2.Range("A5"))
CountIf(Range("B6"),Sheet2.Range("A6"))
CountIf(Range("B6"),Sheet2.Range("A7"))
CountIf(Range("B6"),Sheet2.Range("A8"))
CountIf(Range("B6"),Sheet2.Range("A9"))
CountIf(Range("B6"),Sheet2.Range("A10"))

Does that make it clearer?

 

[Vanda_a]

Yes thanks for your explanation