How to use sumproduct in VBA?

[ZionX]

Good days, can anyone help me on the sumproduct in vba?
below is the code i wrote but not working, and return #VALUE!:

Set rng01 = Range("K4")

p = Cells(1, 1).Value
IC01 = Range("A12").CurrentRegion.Rows.count

For count01 = 12 To 300
Set rng02 = Range(Cells(p, count01), Cells(IC01, count01))

Cells(1, count01) = Application.SumProduct(rng01, rng02)

Next count01

Thanks for help and assist.

 

[cgcamal]

Hi Zionx,

First you need to be sure you have the same dimensions in arrays that will be arguments, see these remarks in function help.

Remarks:

• The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value.
• SUMPRODUCT treats array entries that are not numeric as if they were zeros.

http://office.microsoft.com/en-us/e...ion-HP010342935.aspx?CTT=5&origin=HA010342655

If you continue with some problems please show sample of your sheet.

Hope this helps,

Regards.

 

[CHStriker]

It's a little trickier if you want to use the (string = range1)* range2 variation. Here is the syntax:

Worksheets("Sheet1").Cells(i, j + 2) = Evaluate("SUMPRODUCT(--(range1=$B" & i & ")*(range2=" & j & ")*range3)")

the "i" and "j" are the variables in a double loop.

I think thats enough to keep you going. If you have a hard time making it work post your code here and we can test it.