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

    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

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

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

    Both of them give me errors.

    Help please

  • 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...


    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:

    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


    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:

    1. Cells(1, 1).FormulaR1C1 = "=SUMPRODUCT((R[19]C[10]:R[199]C[10]=1)*(R[19]C[7]:R[199]C[8]=1))"
  • Re: Sumproduct In Vba

    But, this isn´t running either.

    1. MsgBox Application.WorksheetFunction.SumProduct((R[19]C[10]:R[199]C[10]=1)*(R[19]C[7]:R[199]C[8]=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??

    Thanks guys for your help

  • 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.