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

   ActiveCell.FormulaR1C1 = "=SUMPRODUCT(('C:\MrExcel\[Closing Stock.xlsx]closing stock-PS'!C3)*('C:\MrExcel\[Closing Stock.xlsx]closing stock-PS'!c1=rc[-4]))"

You will need to change the file path in the formula to suit.

 

[Joshua Kao]

hmm, does it need to be in that specific location? or if its in a different folder, would it prompt to select the file?

 

[Fluff]

You just need to change the path to where the workbook is located.

 

[Joshua Kao]

When i run the macro, even with both workbooks open, im getting a #value error.

 

[Fluff]

Can you post the code you are using.

 

[Joshua Kao]

This is the whole sumifs portion that im using it for

                   irow = 2
    Do While Cells(irow, 1).Value <> ""
    Cells(irow, 5).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(('C:\Users\Joshua Kao\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!C3)*('C:\Users\Joshua Kao\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!c1=rc[-4]))"
          Cells(irow, 6).Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIFS(Master!C[-3],Master!C[-5],ComparisonM2K!RC[-5],Master!C[-2],ComparisonM2K!RC[-3])"
            Cells(irow, 7).Select
    ActiveCell.FormulaR1C1 = "=(RC[-2]/RC[-1])*100-100"
         irow = irow + 1
         Loop[CODE=vba]

[/CODE]

 

[Fluff]

Do you have any text or formulae that return "" in col C?

 

[Joshua Kao]

that part is saying that if C is filled to execute the below formulas, if its blank then it wont execute it.

 

[Fluff]

Apologies, I wasn't clear, I meant is there any text or formula that return "" on col C of the closing stock-PS sheet?

 

[Joshua Kao]

oh, there is none, every row in the closing stock that has data is a numerical value in column C.