countif when range to count is in a different workbook

[KarenF]

Hi,

I have a workbook which tracks when a certain person has applied for
funding. I have other separate workbooks for each type of funding, and when
someone applies, I need to check the master workbook and have a formula
return how many times their ID appears on the list.

This is my current formula:

=COUNTIF('[Applicant Information.xls]applicants''
applications'!$A$3:$A$65000,A6)

This works great when the Applicant Information.xls file is open, but causes
a #Value! error when it is not. Upon opening my specific funding worksheets
I update links but I keep getting an "Error - non-defined or non-rectangular
name" status when I check the source.

Any ideas? Should all countif formulae rely on an open workbook? All of my
other formulae relating to a closed workbook is now working fine.

If anyone could help, it would be much appreciated.

Thanks,

Karen.

 

[Dave Peterson]

There are a lot of functions that don't work with close workbooks:

=countif(), =sumif(), =indirect() are a few.

With the file open:

=sumproduct(--(
'[Applicant Information.xls]applicants''applications'!$A$3:$A$65000=A6))

And when you close the file, excel will add the path, but the formula will still
calculate correctly.

Remember that =sumproduct() can't use the whole column.

Hi,

I have a workbook which tracks when a certain person has applied for
funding. I have other separate workbooks for each type of funding, and when
someone applies, I need to check the master workbook and have a formula
return how many times their ID appears on the list.

This is my current formula:

=COUNTIF('[Applicant Information.xls]applicants''
applications'!$A$3:$A$65000,A6)

This works great when the Applicant Information.xls file is open, but causes
a #Value! error when it is not. Upon opening my specific funding worksheets
I update links but I keep getting an "Error - non-defined or non-rectangular
name" status when I check the source.

Any ideas? Should all countif formulae rely on an open workbook? All of my
other formulae relating to a closed workbook is now working fine.

If anyone could help, it would be much appreciated.

Thanks,

Karen.

 

[KarenF]

Thanks again Dave.

Yet again, a problem solved!

It works fine.

Thanks for your help.

Take care,

Karen

There are a lot of functions that don't work with close workbooks:

=countif(), =sumif(), =indirect() are a few.

With the file open:

=sumproduct(--(
'[Applicant Information.xls]applicants''applications'!$A$3:$A$65000=A6))

And when you close the file, excel will add the path, but the formula will still
calculate correctly.

Remember that =sumproduct() can't use the whole column.

Hi,

I have a workbook which tracks when a certain person has applied for
funding. I have other separate workbooks for each type of funding, and when
someone applies, I need to check the master workbook and have a formula
return how many times their ID appears on the list.

This is my current formula:

=COUNTIF('[Applicant Information.xls]applicants''
applications'!$A$3:$A$65000,A6)

This works great when the Applicant Information.xls file is open, but causes
a #Value! error when it is not. Upon opening my specific funding worksheets
I update links but I keep getting an "Error - non-defined or non-rectangular
name" status when I check the source.

Any ideas? Should all countif formulae rely on an open workbook? All of my
other formulae relating to a closed workbook is now working fine.

If anyone could help, it would be much appreciated.

Thanks,

Karen.