SUMIFS across multiple workbooks only works when workbooks are open.

[StaceyVECL]

I have multiple workbooks (separate retailers) that I need to pull information from into one bigger workbook for reporting purposes.

I need the formula to calculate the date range and the product name from the retailer's workbook. The SUMIFS formula works but ONLY if I have all of the individual retailers workbooks open at the same time. Is there a work around for this?

I have moved all of the individual workbooks into the same folder as the bigger reporting workbook but it hasn't helped.

 

[Fluff]

You can use sumproduct rather than sumifs as it will work with closed workbooks.

 

[StaceyVECL]

You can use sumproduct rather than sumifs as it will work with closed workbooks.

I did see that solution somewhere, but when I enter it into excel it only gives me "array 1", "array 2". I need it to look up dates between 2 ranges, and product names within those rangers. I'm not sure how to enter that into the sumproduct formula.

 

[Fluff]

Very difficult to help any further as you have not given any information.

 

[StaceyVECL]

Very difficult to help any further as you have not given any information.

This is what I have using the SUMIFS - =SUMIFS('workbook1'Orders'!$D$4:$D$10999,'workbook1'Orders'!$A$4:$A$10999,">="&$C$3,'workbook1'Orders'!$A$4:$A$10999,"<="&$C$4,'workbook1'Orders'!$B$4:$B$10999,"TVD200WUC-ML")

D = the number of units sold
A = date of order
C3 = the start date to look up (this is in reports workbook)
C4 = the end date to look up (this is in reports workbook)
B = "product name"

How would I put this into a sumproduct?

 

[Fluff]

Thanks for that, it would be like

=SUMPRODUCT(('workbook1'Orders'!$A$4:$A$10999>=$C$3)*('workbook1'Orders'!$A$4:$A$10999<=$C$4)*('workbook1'Orders'!$B$4:$B$10999="TVD200WUC-ML")*('workbook1'Orders'!$D$4:$D$10999))

although the workbook references are wrong.

 

[StaceyVECL]

Thanks for that, it would be like

=SUMPRODUCT(('workbook1'Orders'!$A$4:$A$10999>=$C$3)*('workbook1'Orders'!$A$4:$A$10999<=$C$4)*('workbook1'Orders'!$B$4:$B$10999="TVD200WUC-ML")*('workbook1'Orders'!$D$4:$D$10999))

although the workbook references are wrong.

THANK YOU SO MUCH!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[StaceyVECL]

You're welcome & thanks for the feedback.

Sorry another question: will this only work if there is data in the cells? For example, there is only data up to row 46 so does my formula need to stop at row $46? or does it work with blank cells?

 

[Fluff]

If the cells are totally empty then it will work, if they contain a formula that returns "" you will need to use

=SUMPRODUCT(('workbook1'Orders'!$A$4:$A$10999>=$C$3)*('workbook1'Orders'!$A$4:$A$10999<=$C$4)*('workbook1'Orders'!$B$4:$B$10999="TVD200WUC-ML"),'workbook1'Orders'!$D$4:$D$10999)