Countif/Sumif with linked workbook

[Chill]

Hi,


When linking a spreadsheet to another workbook using a countif formula, the spreadsheet requires the linked workbook to be open for the formula to work. If the linked workbook is closed, VALUE is returned in the formula.


Not sure if this a quirk to 2007 or otherwise. Has anybody else come up against this and more importantly, found a way around it?


Cheers

Chris

 

[keymaster]

Did you try using the full workbook path in the range? It should work even when the file is closed.. (I have not tested this)

 

[Clarity]

I haven't experienced this myself, but have had a look though some other forums. It would appear that this is a problem with the sumif and countif formulae.


One suggested solution is the SUMPRODUCT formula.

 

[Clarity]

I've just had a play with SUMPRODUCT. The only way I can see that you can use SUMPRODUCT to return a count will be to use a helper column in your data tab in your linked workbook with a list of ones (and then sum the ones) or by doing the count in the linked work book and then using VLOOKUP.


Hopefully someone will have a more elegant solution.

 

[keymaster]

Okay, so I tried the countif with external workbook reference and it wont work when the workbook is closed (even though full path is mentioned). That is when I tried the SUMPRODUCT as "Clarity" suggested, this is what I get...


=SUMPRODUCT(--(('ACTUAL PATH[Book1.xls]Sheet3'!$B$3:$B$14)="a"))


and it worked and returned the count of values in range B3:B14 matching "a". Sumproduct with one argument works like countif.


Alternatively you can use SUM as well but you need to CTRL+SHIFT+Enter the formula.

=SUM(--(('ACTUAL PATH[Book1.xls]Sheet3'!$B$3:$B$14)="a"))

 

[Chill]

Thanks both, useful info about the SUMPRODUCT. I've managed to get the COUNTIF to work using an array formula i.e


{=count(if('Z:....YF218.xlsb'!A2:A10000=A1,'Z:......YF218.xlsb'!A2:A10000))}

 

[Hui]

There is a very good article and discussion at Daily Dose of Excel about access to data on closed workbooks


http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/