Count Q

S

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
 
G

Gary''s Student

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

Harlan Grove

Teethless mama said:
=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"<>0"))
Click to expand...
....

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).
 
S

Sandy Mann

Teethless mama said:
=SUM(COUNTIF(INDIRECT({"A1","C1","E1"}),"<>0"))
Click to expand...

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

[email protected]
[email protected] with @tiscali.co.uk
 
Top