Thanks Dave. Tried your idea:

=SUMPRODUCT(--('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa.
Tulot'!C13),'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40)

but still get #VALUE returned.

Would there be a problem if the text to be matched or the text to be
searched is actually a formula to return text from another location. It
works fine with SUMIF, but of course only when the other workbooks are open,
which is the problem.

Off to bed, so will check for any replies in morning. Thanks for your help,
I will be dreaming about this problem all night, my wife is right, I am crazy!

"Dave Peterson" wrote:

First open your workbook. It'll make creating the formula easier and excel will
adjust the formula when you close the workbook.

=sumproduct(
--('[2007 draft Tax Documents.xls]Maa. Tili'!$C$6:$C$40='Maa. Tulot'!C13),
'[2007 draft Tax Documents.xls]Maa. Tili'!$D$6:$I$40)

Notice that the comma ampersand is now a plain old equal sign.

A Taxed Mind wrote:

I know that I cannot use SUMif when linking to closed workbooks. I have the
following formula which only works when the workbook is open:

=SUMIF('C:\Documents and Settings\Kärsälä\My Documents\Farm Documents\Tax
Documents and Spreadsheets\[2007 draft Tax Documents.xls]Maa.
Tili'!$C$6:$C$40,"="&'Maa. Tulot'!C13,'C:\Documents and Settings\Kärsälä\My
Documents\Farm Documents\Tax Documents and Spreadsheets\[2007 draft Tax
Documents.xls]Maa. Tili'!$D$6:$I$40)

What could I do to make this work? I have tried both SUMproduct and SUM(if
but excel returned errors both times, presumably because my typing is so poor.

In case there is an easier way what I am trying to achieve is to match the
text in the range on my summary sheet to the same text in the range in the
annual sheets. When a match is found the adjacent value should be returned.

Thanks in advance for your help.

--

Dave Peterson