SUMIFS Formula - #value showing when other workbook not open

Hi there,

I've set up lots of formulas using the SUMIFS format. However, as well documented, the values don't show up if the master spreadsheet isn't open.

Is there a workaround for this?

I've tried SUMPRODUCT but I can't get that to work as I first need to sum the number of opportunities in a row by the criteria. Formula example here:

=SUMIFS('[Master Spreadsheet GTB, Bauer and O2.xlsx]Opportunities'!$N:$N,'[Master Spreadsheet GTB, Bauer and O2.xlsx]Opportunities'!$Q:$Q,"Feb",'[Master Spreadsheet GTB, Bauer and O2.xlsx]Opportunities'!$F:$F,"Graduate programme",'[Master Spreadsheet GTB, Bauer and O2.xlsx]Opportunities'!$D:$D,"Bauer London Lifestyle")

Any ideas?

Thanks

Hi sktneer,
Thanks for coming back so quickly.
Unfortunately that only brings up response from one of the rows - not data across multiple rows
Any ideas?
Fiona