I am using a SUMIF statement to return values when locating a specific text
string from external files. The SUMIF statement works perfectly if I have
the external files open. However if I don't have the external files open,
all my SUMIF formulas change to #VALUE! Is this a limitation because I'm
searching for text? Is there a way around this error without having to open
all 24 external file links everytime I want to view this report?

Here's what my formula:

=SUMIF('G:\[File.xls]Output 1'!$A:$A,"312 Non-Exempt Labor -
Fixed",'G:\[File.xls]Output 1)'!$B:$B)

Use SMPRODUCT on closed files

=SUMPRODUCT(--('[WB Name.xls]Sheet1'!$A$3:$A$22<""),'[WB
Name.xls]Sheet1'!$D$3:$D$22)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dferguson" wrote in message
...
I am using a SUMIF statement to return values when locating a specific
text
string from external files. The SUMIF statement works perfectly if I have
the external files open. However if I don't have the external files open,
all my SUMIF formulas change to #VALUE! Is this a limitation because
I'm
searching for text? Is there a way around this error without having to
open
all 24 external file links everytime I want to view this report?

Here's what my formula:

=SUMIF('G:\[File.xls]Output 1'!$A:$A,"312 Non-Exempt Labor -
Fixed",'G:\[File.xls]Output 1)'!$B:$B)

Use SUMPRODUCT instead...

=SUMPRODUCT(--('G:\[File.xls]Output 1'!$A2:$A100="312 Non-Exempt Labor -
Fixed"),'G:\[File.xls]Output 1)'!$B2:$B100)

Note that SUMPRODUCT does not allow whole column references.

Hope this helps!

In article ,
dferguson wrote:

I am using a SUMIF statement to return values when locating a specific text
string from external files. The SUMIF statement works perfectly if I have
the external files open. However if I don't have the external files open,
all my SUMIF formulas change to #VALUE! Is this a limitation because I'm
searching for text? Is there a way around this error without having to open
all 24 external file links everytime I want to view this report?

Here's what my formula:

=SUMIF('G:\[File.xls]Output 1'!$A:$A,"312 Non-Exempt Labor -
Fixed",'G:\[File.xls]Output 1)'!$B:$B)

Thanks so much. That worked perfectly!

"Bob Phillips" wrote:

Use SMPRODUCT on closed files

=SUMPRODUCT(--('[WB Name.xls]Sheet1'!$A$3:$A$22<""),'[WB
Name.xls]Sheet1'!$D$3:$D$22)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dferguson" wrote in message
...
I am using a SUMIF statement to return values when locating a specific
text
string from external files. The SUMIF statement works perfectly if I have
the external files open. However if I don't have the external files open,
all my SUMIF formulas change to #VALUE! Is this a limitation because
I'm
searching for text? Is there a way around this error without having to
open
all 24 external file links everytime I want to view this report?

Here's what my formula:

=SUMIF('G:\[File.xls]Output 1'!$A:$A,"312 Non-Exempt Labor -
Fixed",'G:\[File.xls]Output 1)'!$B:$B)

Thanks so much! That worked perfectly!

"Domenic" wrote:

Use SUMPRODUCT instead...

=SUMPRODUCT(--('G:\[File.xls]Output 1'!$A2:$A100="312 Non-Exempt Labor -
Fixed"),'G:\[File.xls]Output 1)'!$B2:$B100)

Note that SUMPRODUCT does not allow whole column references.

Hope this helps!

In article ,
dferguson wrote:

I am using a SUMIF statement to return values when locating a specific text
string from external files. The SUMIF statement works perfectly if I have
the external files open. However if I don't have the external files open,
all my SUMIF formulas change to #VALUE! Is this a limitation because I'm
searching for text? Is there a way around this error without having to open
all 24 external file links everytime I want to view this report?

Here's what my formula:

=SUMIF('G:\[File.xls]Output 1'!$A:$A,"312 Non-Exempt Labor -
Fixed",'G:\[File.xls]Output 1)'!$B:$B)