sumif returns #VALUE! when linked workbook is closed

B

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.
 
B

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

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


BrianL said:
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 said:
Use SUMPRODUCT
 
B

BrianL

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

T. Valko said:
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


BrianL said:
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 said:
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

T. Valko

See this:

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

--
Biff
Microsoft Excel MVP


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

T. Valko said:
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


BrianL said:
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.
 
M

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top