Multiple date based SUMPRODUCT failing

I have the the need to show the sum of the product of sheet 2 on sheet 1 if several conditions are met.

The formula is working except for the first array:

=SUMPRODUCT(--(Bid_Circuits=$A2),--(Bid_Week_End=MONTH(D2)),--(Bid_Week_End=YEAR(D2)),--(Bid_Completed))

When I use XL's evaluate feature, XL seems to find the proper data yet returns #VALUE!

I have no idea where to go from here & I would greatly appriciate any help.

Without looking at your worksheet (too big! - could you upload a smaller sample?):

Whatever data type is in A2 needs to be in Bid_Circuits, too.
Check if numbers are entered/stored as text and convert them to numbers.

Change the defined name for bid_completed to the following:

=OFFSET('SHEET 2'!$F$4,0,0,COUNT('SHEET 2'!$D:$D),1)

Your spreadsheet won't have any #VALUE errors after that change.

If you wish to compare MONTH values you must coerce the named range to MONTH also.

=SUMPRODUCT(--(Bid_Circuits=$A2),--(MONTH(Bid_Week_End)=MONTH(D2)),--(YEAR(Bid_Week_End)=YEAR(D2)),Bid_Completed)

You could also use

=SUMPRODUCT(--(Bid_Circuits=$A2),--(Bid_Week_End-DAY(Bid_Week_End)+1=D2-DAY(D2)+1),Bid_Completed)

We'd need to see the file or at least the RefersTo element of the named ranges.

#VALUE! errors in a double unary can arise from inconsistent array dimensions but underlying error values can also generate errors... in this case it sounds like a case of the former but it's still just an educated guess.

=OFFSET('Bid Mileage'!$C$4,0,0,COUNT('Bid Mileage'!$C:$C),1)
=OFFSET('Bid Mileage'!$D$4,0,0,COUNT('Bid Mileage'!$B:$B),1)
=OFFSET('Bid Mileage'!$B$4,0,0,COUNT('Bid Mileage'!$B:$B),1)

I also wonder how could the formulas be re-written to speed up the calculations in the 2.5 YEAR TRIM CYCLE workbook. The layout should remain the same allowing the user to filter and sort the circuits.

Change Bid_Circuits to use $B:$B as opposed to $C:$C ... column C has two blanks in it which means the ranges are not the same size, use B for all ranges.

The above is the quickest fix based on what you have setup presently.

You won't be able to use your Dynamic Named Ranges if the target file is to be closed, you will need to explicitly define them... eg: instead of Bid_Week_End you would need to use say $B$4:$B$2000 (rept for other names and in all formulae).

EDIT: I should add that the above is based on my assumptions and is not meant as a statement of fact

It depends on how much data you're adding - ie how much excess capacity you would need to add in to your fixed ranges to account for future data... if you're talking a few hundred rows of excess then it won't make a huge difference IMO and there are overheads with Dynamic Named Ranges anyway so the cost will to some extent be offset by their removal.

As for alternatives... well I'm afraid I'm not a fan of external links ... I'm a firm believer in keeping files independent from one another in a "live" sense.
If you need data from file 2 for calculations in file 1 it is IMO best to store it in file 1, if that means copy/paste from one file to another so be it - that process can always be automated via VBA (Mac 2008 excepted!).

Others argue the above is risky given you're not necessarily working with "latest data" etc and as such prefer to link but as you've discovered that brings with it it's own limitations. Others use ADO, external data queries etc

I've always believed in being able to save a network file locally, hop onto a plane and work on the file seamlessly before uploading said file back to the network upon arrival... linked files generally speaking (and I say generally) prevent that kind of thing or at best make it a "faff".