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.

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C6:CU6),5)=3),--(C6:CU6="Y"))

--
Biff
Microsoft Excel MVP


"Jerry" wrote in message
...
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")

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jerry" wrote:
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.

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.

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C6:CU6),5)=3),--(C6:CU6="Y"))

--
Biff
Microsoft Excel MVP


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

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?

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.



--
Biff
Microsoft Excel MVP


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

"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(MOD(COLUMN(C6:CU6),5)=3),--(C6:CU6="Y"))

--
Biff
Microsoft Excel MVP


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