DSUM only works if reference workbook is open

[Jen]

Hello -
I have a formula as follows;
=DSUM('[2004 Data.xls]AA'!$A$1:$V$53,"Issues",Q1:Q2)
=DSUM('C:\My Documents\2005 Budgets\[2004 Data.xls]AA'!
$A$1:$V$53,"Back Copies",Q1:Q2)

The problem is that the formula only works when the
source workbook is open. Otherwise, I get a #VALUE error.

Can anyone help?
Thanks!
Jenni

 

[Frank Kabel]

Hi
you can use SUMPRODUCT instead. e.g.< something like
=SUMPRODUCT(--('[2004 Data.xls]AA'!$A$1:$A$53=Q1),--('[2004
Data.xls]AA'!$B$1:$B$53=Q2),'[2004 Data.xls]AA'!$C$1:$C$53)

assumption: column A and column B are used for the comparison and
column C is summed. Change this to your needs.