Never mind.

I didn't notice that the formula was using d6:i40.



"T. Valko" wrote:

Remove the "--" and replace the comma with *.

=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)

This is where I'd use named ranges!

--
Biff
Microsoft Excel MVP

"A Taxed Mind" wrote in message
...
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


--

Dave Peterson