Countif + External Reference = #Value

H

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?
 
P

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
 
Top