Countif + External Reference = #Value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hall.jeff@gmail.com - 28 Nov 2007 16:14 GMT

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?

Reply to this Message

Peo Sjoblom - 28 Nov 2007 16:28 GMT

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

Signature

Regards,

Peo Sjoblom

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

Reply to this Message

hall.jeff@gmail.com - 28 Nov 2007 16:55 GMT

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

Reply to this Message