Let's try to make it a little simpler.

First, open both workbooks.

Second, don't convert the formula to text. Just copy it from the formula bar.
(Select the whole formula and rightclick|Copy.

Then paste into your post.

But choose only one formula--if we get one working, the others might become much
simpler.

And choose a formula like:

=SUMPRODUCT(
--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$2000=D$2),
'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$2000)

In fact, try this against a smaller range

=SUMPRODUCT(
--('[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$E$1:$E$10=D$2),
'[Property Outgoings 2005-06 Estimates.xls]Sheet1'!$N$1:$N$10)

And check your data to make sure that E1:E10 of that other worksheet actually
equals D2 of the formula worksheet.

Common problems are extra spaces (leading/trailing/embedded) or even numbers in
one worksheet, but text (that look like numbers) in the other.

This formula worked for me when the other workbook was open or closed.

======
Remember =sum(if()) and =sumproduct() cannot use a whole column.

<<snipped