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.

 

[StaceyVECL]

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)

You are a super star, thank you.

 

[Fluff]

My pleasure.

 

[StaceyVECL]

Hi, sorry, me again. I've encountered a new problem.

Can I use sumproduct formulas above but have it look up Qty, SKU, Date and currency. to look up 2 separate columns with text in them for example I need it to the look up the SKU in column L "TVD100WU" and the currency in column P "GBP"

I have input this but am getting a VALUE error.

=SUMPRODUCT(('[Amazon FBA Shipment Reports 2023.xlsx]2023'!$C$2:$C$10999>=$B$1,'[Amazon FBA Shipment Reports 2023.xlsx]2023'!$C$2:$C$10999<=$B$2)*('[Amazon FBA Shipment Reports 2023.xlsx]2023'!$L$2:$L$10999="TVD100WU")*('[Amazon FBA Shipment Reports 2023.xlsx]2023'!$P$2:$P$10999="GBP"),'[Amazon FBA Shipment Reports 2023.xlsx]2023'!$O$2:$O$10999)

C = Date
L = SKU
P - Currency
O - Qty

 

[Fluff]

Each criteria should go in it's own brackets, but your 1st two criteria are in the same set of brackets.

 

[StaceyVECL]

Each criteria should go in it's own brackets, but your 1st two criteria are in the same set of brackets.

Thank you. Sorry, its been a long week.

 

[Fluff]

You're welcome.