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 k1:o1 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value 0. Thanks John :)

=COUNTIF(A1:E1,"0")+COUNTIF(K1:O1,"0")+COUNTIF(U 1:Y1,"0")

Tyro



"Johnny1r" wrote in message
. ..
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 k1:o1 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value 0. Thanks John :)

Try this:

=COUNTIF(A1:E1,"0")+COUNTIF(K1:O1,"0")+COUNTIF(U 1:Y1,"0")

--
Biff
Microsoft Excel MVP


"Johnny1r" wrote in message
. ..
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 k1:o1 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value 0. Thanks John :)

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 wrote:

Try this:

=COUNTIF(A1:E1,"0")+COUNTIF(K1:O1,"0")+COUNTIF(U 1:Y1,"0")

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Johnny1r" wrote in message
...
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 wrote:

Try this:

=COUNTIF(A1:E1,"0")+COUNTIF(K1:O1,"0")+COUNTIF(U 1:Y1,"0")

Try this:

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


"Johnny1r" wrote:

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 k1:o1 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value 0. Thanks John :)

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


--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
Try this:

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


"Johnny1r" wrote:

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 k1:o1 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value 0. Thanks John :)

Alternative approach....

=SUMPRODUCT((A1:E10)+(K1:O10)+(U1:Y10))

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

Regards,

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

"Johnny1r" wrote in message
. ..
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 k1:o1 and u1:y1. I am using Excell 2000. In other words how many
cells in those 3 ranges have a value 0. Thanks John :)