Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Macro for SumProduct (2007)

    I have the following sumproduct formula in Column F =SUMPRODUCT(--(C5:C2507="PTS"),--(F5:F2507<0),F5:F2507)

    This formula totals all the values < 0 in col F, where the word "PTS" appears in col C.

    I would like a macro , that will total the values in col F <0 where "PTS" appears in col C three rows after the last value in Col F

    I have attempted to write the code, but cannot get it to work-see code below

    Sub Totals()
    Finalrow = Range("A65536").End(xlUp).Row
    Range("A" & Finalrow + 3).Value = "Total Sales"
    Range("F" & Finalrow + 3).FormulaR1C1 = "=sumproduct(--(c5:C" & Finalrow & "=PTS""),--(f5:F" & Finalrow & "<0""),(f5:F" & Finalrow & ")"

    End Sub

    A compile error appears and the following code is highlighted

    Range("F" & Finalrow + 3).FormulaR1C1 = "=sumproduct(--(c5:C" & Finalrow & "=PTS""),--(f5:F" & Finalrow & "<0")"

    Your assistance in this regard is most appreciated

    Regards

    Howard

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro for SumProduct (2007)

    You have to use quotes consistently. Try
    <code>
    Range("F" & (Finalrow + 3)).FormulaR1C1 = "=SUMPRODUCT(--(C5:C" & Finalrow & "=""PTS""),--(F5:F" & Finalrow & "<0),(F5:F" & Finalrow & ")"
    </code>
    PS you haven't reacted to the reply <post#=736,545>post 736,545</post#> yet.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Macro for SumProduct (2007)

    Hi Hans

    Thanks for thehelp

    Much appreciated

    Regards

    Howard

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •