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.


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


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

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