Error with countif formula on when adding linked spreadsheets in excel 03

Question

Hi, 

I've posted this in a few other forums but not managed to solve the problem so now I'm trying here. I'm having a few problems with a spreadsheet I'm using in excel 2003, I have a spreadsheet (totals) which I'm using to add up 15 other external spreadsheets, if I open the totals spreadsheet and click update I get #VALUE errors, however if I open each of the individual sheets this gets rid of the errors. Having looked round I see that maybe Countif doesn't work on linked spreadsheets and wondered if anyone to suggest an alternative.

An example of the formula I'm using which returns the #VALUE error is

=COUNTIF('G:\Time Sheets\Dave Goodhand[Timesheet Period Ending 040311.xls]Week 1'!A1:U69,"AD01")

This is the only part of the formula I will have to change to wok out the rest Dave Goodhand[Timesheet Period Ending 040311.xlsbecause there are a number of staff.

Any help would be much appreciated Thanks Dave

Answer 1

Hi,

This is happening because COUNTIF() does not work when the source workbook is closed.  When you update links, the source workbook still remains closed and therefore you see the error.

Try this formula instead.

=sumproduct(1*('G:\Time Sheets\Dave Goodhand[Timesheet Period Ending 040311.xls]Week 1'!A1:U69="AD01"))

The sumproduct formula will work across closed files as well.

Answer 2

Sorry for the late response not been at work,

Think I must be missing something (sorry for being thick)

As I've already managed to replace all the =countif with sumproduct I can't copy Ashish's formual in as it will wipe out (I think?)

I now have a lot of these SUMPRODUCT('G:\Time Sheets\Dave Goodhand[Timesheet Period Ending 010511 DG.xls]Week 2'!A1:U69="AD02")

If I type in the extra 1*( and the ) on the end,  it works fine, its just that it gonna take quite a while to change all the cells.

Answer 3

Ok so checked "save external links value" and its ticked, I've gone into edit > links and it brings up a box edit links which listed all the source sheets and the status is marked as unknown.

I tried sumproduct which seems to work brilliant (didn't know about the 1* and - at the end when I tried) I've only tried it for one week of my own timesheet but everthing seems ok, its a bit of a pain having to manually go in and type in the admended formula as they don't seem to copy down.

Could I change the formula I'm using in each persons original spreadsheet to include sumproduct? Guessing this would then sort all my problems, the formula I'm using is

COUNTIF('Week 1'!A1:U69,"AD01")

Thanks for the advice.

Answer 4

Hi,

Yeah that would be sunny Scunthorpe, thinks for the replies I will have a look at the settings on excel when I get back to work on Monday. As for the =sumproduct I think I've tried that already as I'd read somewhere that countif would be a problem but it still return the same error, I'll give it another go on Monday. Thanks

Answer 5

Dave would that be Sunny Scunny?

would you please confirm your setting under options > advanced > save external link values

also what happens when you go to data > edit links > update values?

allllen