Linking cell data to external worksheets

[Raggy]

I have a single sheet that I want to display values based on data in an external workbook.
If I have the other workbook open I can get the data to appear no problem, but if I exit the other workbook the path to the name is added and it wont dispay the data again!

The main sheet I want to collate all these other workbooks to is in the same folder as all the ones that contain the data
L:\Informatics\IT Service Desk\SD Management Team\Performance Reports\SLA Breach Stats\

I had this and worked with the separate workbook open:

=COUNTIF('[Service Desk.xlsx]Sheet1'!$A$2:'[Service Desk.xlsx]Sheet1'!$A$800,0)

but I have now this with the external sheet closed which does not work:

=COUNTIF('L:\Informatics\IT Service Desk\SD Management Team\Performance Reports\SLA Breach Stats\[Service Desk Folders.xlsx]Sheet1'!$A$2:'L:\Informatics\IT Service Desk\SD Management Team\Performance Reports\SLA Breach Stats\[Service Desk Folders.xlsx]Sheet1'!$A$800,0)

Should this work or should I be going about it in a different way, had a google but no clearer.
The objective is to have the one workbook which just collates certain data from other spreadsheets which are extracted from some different software.

This is the data in the separate sheets and I need to find out home many of each SLA type I have (which I can do) but also need to be able to say which ones are suspended or not (which I am not sure how to do). Any help appreciated to point me in the right direction.

 

[Fluff]

The Countif function does not work with closed workbooks, you would need to use sumproduct instead.

 

[Raggy]

That solves that one but now mystified by how to count the amount of say '0''s in column A so long as relevant cell in column C is True

 

[Fluff]

What is the sumproduct formula you are using?

 

[Raggy]

Tried various configuration but so far without much success, here is my latest. The data is now held on separate sheet (Service Desk) within the main workbook for the minute to make things simpler while I get my head around it.

=SUMPRODUCT(ISNUMBER(MATCH('Service Desk'!$A$2:$A$800,{"0"},0))*ISNUMBER(MATCH('Service Desk'!$C$2:$C$800,{"FALSE"},0)))
From this I get FALSE in the cell I want to display the total count of say 0's in column A so long as they are FALSE in column C....hope that makes sense!

 

[Fluff]

Try

=SUMPRODUCT(('Service Desk'!$A$2:$A$800=0)*('Service Desk'!$C$2:$C$800=FALSE))