 # Sumproduct In Vba

• Hey guys,

I have to put from vba code the formula: =SUMPRODUCT((K20:K200=1)*(H20:I200=1)), in every sheet, for getting the result, and remove the formula. Getting and removing is ok : D , but i have some problems putting the formula.
First i tried

Code
1. Cells(1,1).Formula = "=SUMAPRODUCTO((K20:K200=1)*(H20:I200=1))"

I think is correct, but returns an error. What is more strange, that if i click on the cell and press enter, then the formula runs. Anybody knows whats happening?
Another try

Code
1. Application.WorksheetFunction.SumProduct((Range("K20:200") = 1) * (Range("H20:I200") = 1))

Code
1. Application.WorksheetFunction.SumProduct(K20:200 = 1) * (H20:I200 = 1))

Both of them give me errors.

• Re: Sumproduct In Vba

Not running... And i cant understand the usage. Is not:

Code
1. Cells(1, 1).Formula = "=SUMAPRODUCTO((K20:K200=1)*(H20:I200=1))"
2. Application.Evaluate ("A1")
• Re: Sumproduct In Vba

Quote from parrimin

Not running... And i cant understand the usage. Is not:

Code
1. Cells(1, 1).Formula = "=SUMAPRODUCTO((K20:K200=1)*(H20:I200=1))"

This seems OK to me.

• Re: Sumproduct In Vba

It seems Ok to me too, but not running. Yes i also tested to force to calculate.

Code
1. Range("A1").calculate

, but... some more suggestions?

• Re: Sumproduct In Vba

Just a note - you are missing the letter "K" in the above formulas - but I dont think this is your problem...

Ger

Check out our new reputation system. Click on the Like button under the post!
_______________________________________________

[SIZE=8px]There are 10 types of people in the world. Those that understand Binary and those that dont.

Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

The BEST Lookup function of all time

Dynamic Named Ranges are your bestest friend[/SIZE]
_______________________________________________

• Re: Sumproduct In Vba

Ops, It´s true "k" was missing. Now im trying:

Code
1. MsgBox Application.WorksheetFunction.SumProduct("(K20:K200 = 1) * (H20:I200 = 1)")
2. MsgBox Application.WorksheetFunction.SumProduct((K20:K200 = 1) * (H20:I200 = 1))
3. MsgBox Application.WorksheetFunction.SumProduct((Range("K20:K200") = 1) * (Range("H20:I200") = 1))
4. MsgBox Application.WorksheetFunction.SumProduct((Range("K20:K200").value = 1) * (Range("H20:I200").value = 1))

but dont work anything!
I think i will have to compare all rows one on one

• Re: Sumproduct In Vba

try:

Code
1. Cells(1,1) = "=SUMAPRODUCTO((K20:K200=1)*(H20:I200=1))"

BTW: have you tried recording ?

In formulae, depending on your locale, you might have to replace ; with , or vice versa.

• Re: Sumproduct In Vba

Yeahhhh, great h1h, as always happens, the solution was the most obvious, recording!

The solution is:

Code
1. Cells(1, 1).FormulaR1C1 = "=SUMPRODUCT((RC:RC=1)*(RC:RC=1))"
• Re: Sumproduct In Vba

Its been a while since I looked at this but, from memory, the only way to get Sumproduct to work in VBA is to use the approach that Kris suggested - that is, Evaluate.

• Re: Sumproduct In Vba

But, this isn´t running either.

Code
1. MsgBox Application.WorksheetFunction.SumProduct((RC:RC=1)*(RC:RC=1))

Well, if the previous code runs, i dont need this las solution, i can remove cell "A1" when i get the data, but, just out of curiosity, how to make run Application.WorksheetFunction.SumProduct for this purpose??

• Hi guys i noticed this is an old post but still maybe someone can find this useful:

The code should have this form: if you want to use all the values from a specific column just replace Range("K20:K200") with Columns("K:K")

MsgBox Application.WorksheetFunction.SumProduct(Range("K20:K200"), Range("H20:H200"), Range("I20:I200"))

This is all the code you need.

• Hi,

When it comes to Sumproduct in VBA ... Krishnakumar is 100% right ...

Evaluate is very simple and extremely effective ... :smile:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner... 