Countif + External Reference = #Value

[hall.jeff]

It appears that the countif function (along with sumif and others i'm
betting) do not function properly with external links. The only
workaround I've been able to find is to write a macro to open the
various externally referenced sheets and then close them. Is there a
better way for this?

 

[Peo Sjoblom]

If you mean external linked workbooks that are closed then COUNTIF won't
work but you can use SUMPRODUCT instead of both COUNTIF/SUMIF


=COUNTIF(A1:A10,">0")

=SUMPRODUCT(--(A1:A10>0))


will return the same result


=SUMIF(A1:A10,">0",B1:B10)

=SUMPRODUCT(--(A1:A10>0),B1:B10)

will return the same result


--


Regards,


Peo Sjoblom

 

[hall.jeff]

Thanks... That worked... What a wonderfully undocumented feature