software

I need to count all the nonconsecutive cells in a given range that contain a
specific # 1-9. I have a formula that counts all the cells in the given
range: =countif(c4:k12,quot;1quot;). I need to modify it to count only the cells
d5,d8,d11,g5,g8,g11,j5,j8,j11 that contain the #1. I know there must be a way
but i am not familure enough with creating formulas to figure it out. Please
help. I am using excel 2002. jp

=SUMPRODUCT(COUNTIF(INDIRECT({quot;D5quot;,quot;D8quot;,quot;D11quot;,quot;G5quot; ,quot;G8quot;,quot;G11quot;,quot;J5quot;,quot;J8quot;,quot;J11
quot;}),1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;jpquot; gt; wrote in message
...
gt; I need to count all the nonconsecutive cells in a given range that contain
a
gt; specific # 1-9. I have a formula that counts all the cells in the given
gt; range: =countif(c4:k12,quot;1quot;). I need to modify it to count only the cells
gt; d5,d8,d11,g5,g8,g11,j5,j8,j11 that contain the #1. I know there must be a
way
gt; but i am not familure enough with creating formulas to figure it out.
Please
gt; help. I am using excel 2002. jp
If you have the free morefunc.xll add-in installed:

=SUMPRODUCT((ARRAY.JOIN(D5,D8,D11,G5,G8,G11,J5,J8, J11)=1) 0)

Otherwise:

=SUM(IF(MOD(ROW(D5:J11)-ROW(D5) 0,3)=0,IF(MOD(COLUMN(D5:J11)-COLUMN(D5) 0,3)=0,(D5:J11=1) 0)))

which must be confirmed with control shift enter, not just with enter.

jp wrote:
gt; I need to count all the nonconsecutive cells in a given range that contain a
gt; specific # 1-9. I have a formula that counts all the cells in the given
gt; range: =countif(c4:k12,quot;1quot;). I need to modify it to count only the cells
gt; d5,d8,d11,g5,g8,g11,j5,j8,j11 that contain the #1. I know there must be a way
gt; but i am not familure enough with creating formulas to figure it out. Please
gt; help. I am using excel 2002. jp