Multiple non-consecutive rows
 

How do I include multiple non-consecutive rows in a COUNTIF function. I need
to check rows (4,7,10,13 thru 97 (every third row)) for a value. I used =
COUNTIF (4:4, "Yes") and it returns the correct number. I have tried several
methods to include the 32 row I need to check for the value "Yes" and it will
not allow the multiple rows

Thanks in advance.
--
RW

Multiple non-consecutive rows
 

Try

=SUMPRODUCT(--($B$4:$B$97="Yes"),--(MOD(ROW($B$4:$B$97),3)=1))

adapt to fit your range


--
Regards,

Peo Sjoblom



"RW" wrote in message
...
How do I include multiple non-consecutive rows in a COUNTIF function. I
need
to check rows (4,7,10,13 thru 97 (every third row)) for a value. I used
=
COUNTIF (4:4, "Yes") and it returns the correct number. I have tried
several
methods to include the 32 row I need to check for the value "Yes" and it
will
not allow the multiple rows

Thanks in advance.
--
RW

Multiple non-consecutive rows
 

Try this:

Do you really need to use the entire row as a range reference?

=SUMPRODUCT((MOD(ROW(A$4:IV$97)-ROW(A$4),3)=0)*(A$4:IV$97="Yes"))

Biff

"RW" wrote in message
...
How do I include multiple non-consecutive rows in a COUNTIF function. I
need
to check rows (4,7,10,13 thru 97 (every third row)) for a value. I used
=
COUNTIF (4:4, "Yes") and it returns the correct number. I have tried
several
methods to include the 32 row I need to check for the value "Yes" and it
will
not allow the multiple rows

Thanks in advance.
--
RW

Multiple non-consecutive rows
 

Yes at least to a resonable extent. We are tracking cleared reports from the
State. A single school may have numerous reports that are not "clean" (A
"Yes" response) and then they will have one report that is clean. We are
trying to track how many schools have clean reports for a 40th day report and
also a 100th day report.
--
RW


"T. Valko" wrote:

Try this:

Do you really need to use the entire row as a range reference?

=SUMPRODUCT((MOD(ROW(A$4:IV$97)-ROW(A$4),3)=0)*(A$4:IV$97="Yes"))

Biff

"RW" wrote in message
...
How do I include multiple non-consecutive rows in a COUNTIF function. I
need
to check rows (4,7,10,13 thru 97 (every third row)) for a value. I used
=
COUNTIF (4:4, "Yes") and it returns the correct number. I have tried
several
methods to include the 32 row I need to check for the value "Yes" and it
will
not allow the multiple rows

Thanks in advance.
--
RW