#VALUE! Error on linked workbooks

[excelbytes]

I have two linked workbooks. Workbook A is calculating a COUNTIF value from Workbook B. Both workbooks are in the same folder on my computer. When I open Workbook A and still have Workbook B closed, I get a #VALUE! error, even though I have File>Options>Advanced>General "Ask to update automatic links" unchecked. What's the reason? When I open Workbook B, the error is resolved.

 

[jasonb75]

There are a number of functions that do not work with closed workbooks, countifs is one of them.

 

[excelbytes]

Thanks, Jason. I wasn't aware of that.

 

[jasonb75]

You're welcome

If you need it to work with the other workbook closed then the option would be to use sumproduct, but that would be much slower to calculate, especially with large data volumes.

I made the mistake with SUMIFS quite recently when I suggested it here, not realising that it didn't work with a closed workbook (having never tried using it with one previosuly)

For reference, the ones that I know of are all of the mathematical 'IF' functions, INDIRECT, and some, if not all dynamic array functions (I haven't tried them across workbooks personally, but have seen the problem discussed in another thread recently).

There may be others as well.

I did look to see if there was a definitive list, but nothing came up in a quick search.

 

[Alex Blakenburg]

You're welcome

If you need it to work with the other workbook closed then the option would be to use sumproduct, but that would be much slower to calculate, especially with large data volumes.

I made the mistake with SUMIFS quite recently when I suggested it here, not realising that it didn't work with a closed workbook (having never tried using it with one previosuly)

For reference, the ones that I know of are all of the mathematical 'IF' functions, INDIRECT, and some, if not all dynamic array functions (I haven't tried them across workbooks personally, but have seen the problem discussed in another thread recently).

There may be others as well.

I did look to see if there was a definitive list, but nothing came up in a quick search.

This is not perhaps not a definitive but it covers a lot of them.
Functions that Can Access Closed Workbooks