COUNTIF on closed Workbooks ??

[smrogers]

Hi,

I am trying to count the values of sickness for each employee based on their annualised attendance sheet.

We have an attendance map for the 12 months which shows all the different types of absence an individual has.

I want to in a different spreadsheet count the values of "S" in these closed workbooks, which will in effect give me the number of sickness occurances for each month, by employee.

The formula works fine if I have the workbook I am wanting to query open but I need to be able to query about 160 individual spreadsheets for all of our employees.

Workbook open Formula
e.g. =COUNTIF('[employees_name.xlsm]Attendance Map'!$I$10:$J$40,"s")/2

I have tried a number of methods to try get this to work with the workbook closed.

Workbook closed Formula
e.g. COUNT(IF('T:\TimeSheets\Attendance Sheets 2011\department\[employee_name.xlsm]Attendance Map'!$C$10:$D$40,"s"))/2

But I can't seem to get the correct data out ?

Has anyone got any experience on doing a countif on a closed workbook ?

Any help or pointers would be greatley appreciated.

Thanks in advance.

Steve

 

[Andrew Poulsom]

COUNTIF requires that the workbook is open. Try:

=SUMPRODUCT(--('[employees_name.xlsm]Attendance Map'!$I$10:$J$40="s"))/2

 

[smrogers]

hi,

thanks for your response.....

I have been having a play with this and seem to have got the following to work.....

=SUMPRODUCT(--('T:\TimeSheets\Attendance Sheets 2011\department\[employee_name.xlsm]Attendance Map'!$I$10:$J$40="S"))

So thanks for your help......

Cheers

Steve