Changing a Sumifs to a Sumproduct

[Joshua Kao]

Hi, could someone please help me convert the sumifs formula in VBA, to a sumproducts please? Thank you.

ActiveCell.FormulaR1C1 = _
"=SUMIFS('Closing Stock.xlsx'!C3,'Closing Stock.xlsx'!C1,RC[-4])"

 

[Fluff]

Ok, how about

   Dim Pth As String
   Pth = Environ("userprofile")
   
   irow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("E2:E" & irow).FormulaR1C1 = _
        "=SUMPRODUCT(('" & Pth & "\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!R2C3:R1000C3)*('" & Pth & "\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!R2c1:R1000C1=rc[-4]))"
    Range("F2:F" & irow).FormulaR1C1 = _
        "=SUMIFS(Master!C[-3],Master!C[-5],ComparisonM2K!RC[-5],Master!C[-2],ComparisonM2K!RC[-3])"
    Range("G2:G" & irow).FormulaR1C1 = "=(RC[-2]/RC[-1])*100-100"

 

[Joshua Kao]

hmm, now its giving the #value error again

 

[Fluff]

If you select a cell with the formula in it & look at the formula bar, is the formula correct?

 

[Joshua Kao]

oh nvm i forgot to change it from R2 to R3, the data starts from row 3 in the closing stock.

 

[Fluff]

Glad you sorted it & thanks for the feedback.