Count Q

[Sean]

How can I count up the number of non "Zeros" in A1, C1, E1?

I can't use a simple COUNTA as I may have "Zeros" in some cells and I
don't want those included in my answer

Thanks

 

[Guest]

=COUNTIF(A1:A1,"<>0")+COUNTIF(C1:C1,"<>0")+COUNTIF(E1:E1,"<>0")
or
=(A1<>0)+(E1<>0)+(C1<>0)

 

[Sean]

=COUNTIF(A1:A1,"<>0")+COUNTIF(C1:C1,"<>0")+COUNTIF(E1:E1,"<>0")
or
=(A1<>0)+(E1<>0)+(C1<>0)
--
Gary''s Student
gsnu200710







- Show quoted text -

Thanks GaryS

 

[Guest]

=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"<>0"))

 

[Harlan Grove]

=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"<>0"))

....

Shorter,

=COUNT(1/A1,1/C1,1/E1)

More flexible,

=SUMPRODUCT(FREQUENCY(x,{-1E-300;0}),{1;0;1})

where x is (A1,C1,E1) (INCLUDING the parentheses).

 

[Sandy Mann]

=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"<>0"))

It looks like it should work but in XL97 it returns 3 for me because
=COUNTIF(A1,"<>0") returns 1

I don't know if it is the same in all versions.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk