Why am I getting this error? (COUNTIF with 3D reference)

[Big Monkey]

I have the following formula in worksheet 2

=COUNTIF('2'!$C$2:$C$65536, 1)

I just recently discovered 3D references and tried to change it to:

=COUNTIF('1:2'!$C$2:$C$65536, 1)

When I add worksheet 1 to the equation, I get a #VALUE error.

Why is this happening? The COUNTA function seems to work fine, but COUNTIF won't

BTW, I tried the 1 with and without quotes, didn't make a difference (didn't think it would, but I gave it a shot anyway.)

 

[Big Monkey]

Doh!

It seems that COUNTIF is not one of the supported functions using 3D references.

This really throws a kink for me.

Anyone know of any workarounds?

 

[Brian from Maui]

Try,

=SUMPRODUCT(COUNTIF(INDIRECT("'"&sheetlist&"'!A2:A10"),B2))

Where sheetlist is a named range containing your sheet list and B2 the criteria.