Countif function with a closed workbook

[jimmydc716]

I have a lot of data in a workbook that i want to archive. the issue is i need to determine how many times a specific number appears on that to conduct a trend analysis. Is there a way to conduct a search of how many times a value appears in a different closed workbook? I do know that Vlookup works with closed files, i can not get countif. also does index work as well?

 

[Oaktree]

SUMPRODUCT works on closed workbooks.

Instead of =COUNTIF('C:\Excel file\[test.xlsx]Sheet1'!A1:A100,"Look for this"), try =SUMPRODUCT(--('C:\Excel file\[test.xlsx]Sheet1'!A1:A100 = "Look for this"))