Tracking down #value error in Sumproduct result

[JAlbers]

Greetings all!

I am working on a dynamic reference for our accountant to allow him to get cost data on different items being produced. I have a database that has production data entered for each day and I am trying to pull the data for various time frames based on a =NOW() value in A1.

I am using the SUMPRODUCT function as the file with the data may or may not be open when the lookup is performed, and this is the formula I am having some difficulty with:

=SUMPRODUCT(--('Production Analysis v2.00.xlsm'!FG_Number=D5)*--(MONTH('Production Analysis v2.00.xlsm'!FillDate)=(MONTH(A1)))*--('Production Analysis v2.00.xlsm'!Completed)))

I don't have any problem with the FG_Number, but the FillDate seems to be throwing a #VALUE error. I don't know how to track that down to fix any bad data. The data all looks correct, but either I wrote the formula wrong or there is bad data hiding in the range.

I would appreciate any help.

Jake

 

[texasalynn]

sounds like you have "text" in that area. Also make sure there aren't any blanks

 

[JAlbers]

Thanks, I think that may be the problem. The named range is the entire column including the header.