# COUNTIF On Closed Workbook Gives #VALUE!

• I am using the following formula:

=COUNTIF('C:\Users\Chris Edwards\Documents\Work\New Stuff\July\Team 1\[Anna.xls]Input Sheet'!AP\$22:AP\$671,B6)

If I open this sheet when "Anna" is not open, I get #VALUE! errors with the message "A value used in the formula is of the wrong data type."

When I open "Anna", all of the formula's then calculate and give me the correct results.

What could be causing this and how do I fix it?

Thanks!

• Re: Value Error When Linking A Countif Function

Someone correct me if I'm wrong, but this is always a problem when using sumif and countif. I've always used a procedure to auto open, then close any spreadsheets referred to in either of those functions where applicable. Lately I've avoided sumif and countif in any situation where I'm referring to an external workbook.

If there really is a workaround, I'd love to know!

• Re: Value Error When Linking A Countif Function

Yep, that's a limitation with both Sumif/Countif - the source workbook needs to be open. You can get around this by using Sumproduct instead - Chris in your case this would be:

=SUMPRODUCT(('C:\Users\Chris Edwards\Documents\Work\New Stuff\July\Team 1\[Anna.xls]Input Sheet'!AP\$22:AP\$671=B6)+0)

Now, I see from your profile you are using xl97. I can't guarantee that this will definitely work in 97 (I'm pretty sure it will) - you will just have to give it a try

Richard

• Re: Value Error When Linking A Countif Function

I would reference the needed range and pull the values into the needed Workbook, then use a normal COUNTIF OR SUMIF.

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!