Countif
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.
|