Another which count function?

[Johnny1r]

Hello again and thanks for the fast response to my last question. I
have another question that I have been trying to find an answer for a
couple of years now, maybe you can help. I have a row of numbers A:1
to Y:1 that I have copied and pasted as values from another sheet in my
workbook. Some of these cells are a result of formulas so when I paste
as values those that had no results show up as 0's, this is fine. My
problem is I would like to count the number of cells of 3 ranges that
have a value >0. I hide the 0's but they still count Some cells are
blank some have 0's and some have numbers. I only want to count the
cells with numbers in those 3 ranges together. The ranges are a1:e1
and k11 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value >0. Thanks John

 

[Tyro]

=COUNTIF(A1:E1,">0")+COUNTIF(K1:O1,">0")+COUNTIF(U1:Y1,">0")

Tyro

 

[T. Valko]

Try this:

=COUNTIF(A1:E1,">0")+COUNTIF(K1:O1,">0")+COUNTIF(U1:Y1,">0")

 

[Johnny1r]

Thans again fella's it worked like a charm. I never thought about just
adding them together, dah!! You guys are great Thanks again John

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Teethless mama]

Try this:

=SUM(COUNTIF(INDIRECT({"A1:E1","K1:O1","U1:Y1"}),">0"))

 

[T. Valko]

=SUMPRODUCT(--(CHOOSE({1;2;3},A1:E1,K1:O1,U1:Y1)>0))

 

[Ron Coderre]

Alternative approach....

=SUMPRODUCT((A1:E1>0)+(K1:O1>0)+(U1:Y1>0))

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)