Countif + External Reference = #Value

  • Thread starter Thread starter hall.jeff
  • Start date Start date
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?
 
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
 
Back
Top