# Formula returning #N/A error

#### jeepnfl

##### New Member
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?

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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

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!

Replies
12
Views
528
Replies
4
Views
361
Replies
2
Views
337
Replies
6
Views
263
Replies
21
Views
726

1,203,212
Messages
6,054,190
Members
444,708
Latest member
David R__

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back