Formula returning #N/A error

[jeepnfl]

I have one spreadsheet that pulls data from another spreadsheet using this formula:

=SUMPRODUCT(--('[FY12 Main Log macro.xlsm]Sheet1'!$A$2:$A$3000>="01-Nov-11"+0),--('[FY12 Main Log macro.xlsm]Sheet1'!$A$2:$A$3000<"01-Dec-11"+0),--('[FY12 Main Log macro.xlsm]Sheet1'!$C$2:$C$3000=B30), '[FY12 Main Log macro.xlsm]Sheet1'!$O$2:$O$3000)

The result from this formula is #N/A and I wonder if it has something to do with the fact that the range "Sheet1'!$C$2:$C$3000" that this sheet references also uses a formula for it's result.

When I use this formula and the range "Sheet1'!$C$2:$C$3000" is just a value (not a formula) the result is the correct calculation.

I'm not even really sure how to ask this question. Please let me know if you need any further explanation.

Any words of wisdom?

 

[barry houdini]

You'll get #N/A error if there are #N/A errors in the sum range - if so then either eliminate those errors at source by amending the formulas in that range or perhaps try using this "array formula"

=SUM(IF('[FY12 Main Log macro.xlsm]Sheet1'!$A$2:$A$3000>="01-Nov-11"+0,IF('[FY12 Main Log macro.xlsm]Sheet1'!$A$2:$A$3000<"01-Dec-11"+0,IF('[FY12 Main Log macro.xlsm]Sheet1'!$C$2:$C$3000=B30,IF(ISNUMBER('[FY12 Main Log macro.xlsm]Sheet1'!$O$2:$O$3000),'[FY12 Main Log macro.xlsm]Sheet1'!$O$2:$O$3000)))))

confirmed with CTRL+SHIFT+ENTER

 

[jeepnfl]

Wow, that was too easy! I had some #N/A's in the sum range but didn't realize that was a problem, I just assumed those would result in a "false". I fixed those and my other spreadsheet sudenly populated with correct data!

Thank you Mr. Houdini, you helped me escape great frustration!