sumif returns #VALUE! when linked workbook is closed

[BrianL]

I am using the sumif function to return values from a linked workbook. When
the linked workbook is not open, however, the cells using this formula
returns a #VALUE! error. Anyone have any suggestions to fix this?

Thanks.

 

[T. Valko]

Use SUMPRODUCT

 

[BrianL]

SUMPRODUCT doesn't apply here; I just need to sum the entries in one column
that meet my criteria:

=SUMIF('Q:\Shared\blan\Revenue Estimates\[Revenue_Forecast_current.xlsx]Tail
by Month'!$K:$K,E3,'Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail by Month'!$CL:$CL)

 

[T. Valko]

SUMPRODUCT doesn't apply here

Sure it does. Try it. You might be surprised!

The only difference is you can't use entire columns as range references
unless you're using Excel 2007 (but it looks like you are with a xlsx file
type. However, I still wouldn't use entire columns as references *unless*
you're using *every* row)

=SUMPRODUCT(--(your_path_$K1:K100=E3),your_path_$CL1:$CL100)

--
Biff
Microsoft Excel MVP

SUMPRODUCT doesn't apply here; I just need to sum the entries in one
column
that meet my criteria:

=SUMIF('Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail
by Month'!$K:$K,E3,'Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail by Month'!$CL:$CL)

Use SUMPRODUCT

 

[BrianL]

Thanks, Biff. That worked great. Why do you need the double negative (--)
at the beginning of the first array?

SUMPRODUCT doesn't apply here

Sure it does. Try it. You might be surprised!

The only difference is you can't use entire columns as range references
unless you're using Excel 2007 (but it looks like you are with a xlsx file
type. However, I still wouldn't use entire columns as references *unless*
you're using *every* row)

=SUMPRODUCT(--(your_path_$K1:K100=E3),your_path_$CL1:$CL100)

--
Biff
Microsoft Excel MVP

SUMPRODUCT doesn't apply here; I just need to sum the entries in one
column
that meet my criteria:

=SUMIF('Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail
by Month'!$K:$K,E3,'Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail by Month'!$CL:$CL)

Use SUMPRODUCT

--
Biff
Microsoft Excel MVP


I am using the sumif function to return values from a linked workbook.
When
the linked workbook is not open, however, the cells using this formula
returns a #VALUE! error. Anyone have any suggestions to fix this?

Thanks.

 

[T. Valko]

See this:

http://mcgimpsey.com/excel/formulae/doubleneg.html

--
Biff
Microsoft Excel MVP

Thanks, Biff. That worked great. Why do you need the double negative
(--)
at the beginning of the first array?

SUMPRODUCT doesn't apply here

Sure it does. Try it. You might be surprised!

The only difference is you can't use entire columns as range references
unless you're using Excel 2007 (but it looks like you are with a xlsx
file
type. However, I still wouldn't use entire columns as references *unless*
you're using *every* row)

=SUMPRODUCT(--(your_path_$K1:K100=E3),your_path_$CL1:$CL100)

--
Biff
Microsoft Excel MVP

SUMPRODUCT doesn't apply here; I just need to sum the entries in one
column
that meet my criteria:

=SUMIF('Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail
by Month'!$K:$K,E3,'Q:\Shared\blan\Revenue
Estimates\[Revenue_Forecast_current.xlsx]Tail by Month'!$CL:$CL)

:

Use SUMPRODUCT

--
Biff
Microsoft Excel MVP


I am using the sumif function to return values from a linked
workbook.
When
the linked workbook is not open, however, the cells using this
formula
returns a #VALUE! error. Anyone have any suggestions to fix this?

Thanks.

 

[MDI Anne]

I recently discovered the same problem as original post, and I tried using
"sumproduct", but I still get the #VALUE! error.

Anything else I can try?

Thanks!

 

[disturbatore]

so far it still works fine in my Excel 2007.

--
Bernardo