This is a basic example of a problem I have run into.

I have one column, 10 rows deep. Each cell in the ten cells have the letter
X in it. I only want to count how many X's there are in the 1st,3rd,5th,7th
and 9th row. Countif requires a range I believe and does not allow seperate
multiple cells. Are there any other methods of doing this count.

One possibility, assuming data is in A4:A13 and you want to evaluate the 1,
3, 5, 7, and 9th rows:

=SUMPRODUCT(--(MOD(ROW(A4:A13)-ROW(A4)+1,2)<0),
LEN(A4:A13)-LEN(SUBSTITUTE(A4:A13,"x","")))

If you want the even rows change <0 to =0

"Shu of AZ" wrote:

This is a basic example of a problem I have run into.

I have one column, 10 rows deep. Each cell in the ten cells have the letter
X in it. I only want to count how many X's there are in the 1st,3rd,5th,7th
and 9th row. Countif requires a range I believe and does not allow seperate
multiple cells. Are there any other methods of doing this count.

In the real worksheet, the rows are random and there are 300 of them. I need
to be able to select which rows I need to evaluate and they have no specific
pattern. " 1,4,5,12,17,and so on."

"JMB" wrote:

One possibility, assuming data is in A4:A13 and you want to evaluate the 1,
3, 5, 7, and 9th rows:

=SUMPRODUCT(--(MOD(ROW(A4:A13)-ROW(A4)+1,2)<0),
LEN(A4:A13)-LEN(SUBSTITUTE(A4:A13,"x","")))

If you want the even rows change <0 to =0

"Shu of AZ" wrote:

This is a basic example of a problem I have run into.

I have one column, 10 rows deep. Each cell in the ten cells have the letter
X in it. I only want to count how many X's there are in the 1st,3rd,5th,7th
and 9th row. Countif requires a range I believe and does not allow seperate
multiple cells. Are there any other methods of doing this count.

That's the problem with "examples" of your data that aren't. Details are
always important.

Let's say you want rows 1, 4, 7, and 9, try:
=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(A4:A13)-ROW(A4)+1,{1,4,7,9},0))),
LEN(A4:A13)-LEN(SUBSTITUTE(A4:A13,"x","")))

I'm assuming 1,4,7,and 9 are the index number of the items in your table,
not the actual row numbers (ie - the first row of your table could be in cell
A9).

If you are after the actual row numbers 1,4,7,9 use
=SUMPRODUCT(--(ISNUMBER(MATCH(ROW(A4:A13),{1,4,7,9},0))),
LEN(A4:A13)-LEN(SUBSTITUTE(A4:A13,"x","")))

in either case change {1,4,7,9} to whatever you want.


"Shu of AZ" wrote:

In the real worksheet, the rows are random and there are 300 of them. I need
to be able to select which rows I need to evaluate and they have no specific
pattern. " 1,4,5,12,17,and so on."

"JMB" wrote:

One possibility, assuming data is in A4:A13 and you want to evaluate the 1,
3, 5, 7, and 9th rows:

=SUMPRODUCT(--(MOD(ROW(A4:A13)-ROW(A4)+1,2)<0),
LEN(A4:A13)-LEN(SUBSTITUTE(A4:A13,"x","")))

If you want the even rows change <0 to =0

"Shu of AZ" wrote:

This is a basic example of a problem I have run into.

I have one column, 10 rows deep. Each cell in the ten cells have the letter
X in it. I only want to count how many X's there are in the 1st,3rd,5th,7th
and 9th row. Countif requires a range I believe and does not allow seperate
multiple cells. Are there any other methods of doing this count.