COUNTIFS #Value! Error

[arooney88]

Hi everyone. New to the board and looking for a little help on a problem I'm having.
I'm trying to use COUNTIFS to pull data from a seperate workbook's worksheet onto my current workbook I'm using. I'm trying to look in my G column range for a string ("NO") and also my C column range for a string "WFF".

My formula reads
=COUNTIFS('C:\Users\u202786\Desktop\Reporting Information\2013 Testing Results\Testing Results\Aug\[180 Results.xlsm]Traynor'!G4:G683, "NO", 'C:\Users\u202786\Desktop\Reporting Information\2013 Testing Results\Testing Results\Aug\[180 Results.xlsm]Traynor'!C4:C683, "WFF")

It's returning the #Value! error instead of the correct number. Any ideas on why it's not working??

 

[barry houdini]

Welcome to MrExcel arooney88,

COUNTIFS doesn't work with closed workbooks - if you need your formula to work on a closed source workbook change to SUMPRODUCT, i.e.

=SUMPRODUCT(('C:\Users\u202786\Desktop\Reporting Information\2013 Testing Results\Testing Results\Aug\[180 Results.xlsm]Traynor'!G4:G683="NO")*('C:\Users\u202786\Desktop\Reporting Information\2013 Testing Results\Testing Results\Aug\[180 Results.xlsm]Traynor'!C4:C683="WFF"))

 

[arooney88]

THANK YOU! I didn't know that COUNTIFS didn't work with closed workbooks. Thanks for your help!

 

[arooney88]

Is there a formula that does what COUNTIFS does but that works in closed workbooks?

 

[barry houdini]

The SUMPRODUCT formula I posted should do that - didn't it work for you?

 

[arooney88]

Sorry! Yes everything worked. I had a typo in my formula. Everything is good now