Matching List Formula Error

I'm encountering a very frustrating problem with a formula I use for matching a value in another list. I use =IF(COUNTIF('[DifferentWorkbook.xlsx]Sheet1:Sheet5'!$A$4:$J$55,H3)>0,"YES","NO") . The formula works for one sheet but returns a #VALUE? error when selecting all sheets in a different workbook. Why is this and how can I fix it?

Regards

Those 3D references (Sheet1:Sheet5) only work in simple functions like SUM.
But not in countif

I'm afraid you'd likely have to do
COUNTIF1+COUNTIF2+COUNTIF3+COUNTIF4 etc..


Realistically, I'd recommend haing each countif in it's own cell.
Something like
A1: =COUNTIF('[DifferentWorkbook.xlsx]Sheet1'!$A$4:$J$55,H3)
B1: =COUNTIF('[DifferentWorkbook.xlsx]Sheet2'!$A$4:$J$55,H3)
C1: =COUNTIF('[DifferentWorkbook.xlsx]Sheet3'!$A$4:$J$55,H3)
D1: =COUNTIF('[DifferentWorkbook.xlsx]Sheet4'!$A$4:$J$55,H3)
etc..

Then do
=IF(SUM(A1:D1)>0,"YES","NO")

It seems like using COUNTIF is not worth using if the file needs to be open to show the returned value. How else could I go about checking one value against a list of values in another workbook?