Hi Dave

I have made the range 1 to 200 as this will recognise all the rows that it's
looking at. I don't understand why it keeps bringing back a zero value. I
need it to look in one column in the other workbook and if the value matches
that of the workbook the formula is in then I want it to bring back the value
in another column in the other workbook. I need it to be a sum in case this
more than one row with the same value.

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

"Dave Peterson" wrote:

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