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]

Is there a header in that column?

 

[Joshua Kao]

yes there is a header

 

[Fluff]

In that case try

 ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT(('C:\Users\Joshua Kao\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!R2C3:R1000C3)*('C:\Users\Joshua Kao\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!R2C1:R1000C1=rc[-4]))"

Change the R1000 to a number that is large enough to cover your entire data, without going to large.

 

[Joshua Kao]

it works now. Thank you so much!. If you don't mind, i have another question. If i want to copy paste from a closed workbook to the current workbook, what command should i use for it? I have it working when the data workbook is opened, but not when its closed. ive posted my current code below.

    Windows("PHMG Medicine Master.xlsx").Activate
    Range("A2:D2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Consolidated Workbook.xlsm").Activate
    Sheets("ComparisonM2K").Select
    Range("A2").Select
    ActiveSheet.Paste

 

[Fluff]

As that's a totally different question you will need to start a new thread.
With regard to this thread you can get rid of the loop like

   irow = Cells(Rows.Count, 1).End(xlUp).Row
   Range("E2:E" & irow).FormulaR1C1 = _
        "=SUMPRODUCT(('C:\Users\Joshua Kao\Desktop\PHMG\Stock Take\PS\[Closing Stock.xlsx]closing stock-PS'!R2C3:R1000C3)*('C:\Users\Joshua Kao\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]

ah ok thats fair. Thanks for all your help!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Joshua Kao]

sorry, i have another question regarding the code, would it be possible to do something like this

 fnameList = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks (*.xls;*.xlsx;*.xlsm),*.xls;*.xlsx;*.xlsm", Title:="Choose Excel files to merge", MultiSelect:=True)

to select the closing stock workbook instead of it pointing directly to a specific directory? This is because it will not always be my computer that it is on

 

[Fluff]

Will it always be in the same location, just a different user name?

 

[Joshua Kao]

yep, it will always be on C:/users/"username"/desktop/"foldername"/. Its will either be here, or be in the downloads folder.