counting every other cell containing text

  • Thread starter Thread starter Jerry
  • Start date Start date
J

Jerry

I have a long spreadsheet and I have several columns that have text. These
cells are not consecutive but is like every fourth cell and I have to count
them to perform operations like calculating percentages. If i pick a range
of cells (i.e. c6:cu6) the results are going to be wrong because along the
range are other cells that may have the same content.
a b c d e f g h i j k l m n o
0 5 N N Y 7 3 Y N N 6 7 Y N Y
now I want to count c, h, m and do some sort of computation such as
=countif(c,h,m,"Y")
Your assistance is greatly appreciated.
 
.. cells are not consecutive but is like every fourth cell
=countif(c,h,m,"Y")
Click to expand...

Assuming data is in row 2 down,
this expression should achieve the indicative "countif" above,
placed in say, P2:
=SUMPRODUCT((MOD(COLUMN(C2:M2),5)=3)*(C2:M2="Y"))
Copy P2 down

---
 
What are the 5 and 3 in the column side. Is the 5 the size of the block I am
reading and the 3 the third element of that array? I am asking this because
I have to perform the same operation on the following 2 columns. It worked
fine for the first element that I picked up but it does not work for the next
set of columns.
 
What are the 5 and 3 in the column side.
Is the 5 the size of the block I am reading
and the 3 the third element of that array?
Click to expand...

No.

We need to find a pattern that we can apply to the range of cells that tells
the formula which cells in the range to calculate. To see the pattern try
this:

Enter this formula in C1 and copy across to N1.

=MOD(COLUMN(),5)

Your range of interest was columns C, H, M etc. Do you see the pattern
returned by the above formula? The common criteria of that pattern is the
number 3. So, we tell the formula to calculate only those cells related to
3.
 
Back
Top