Multiple non-consecutive rows

[RW]

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.

 

[Peo Sjoblom]

Try

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

adapt to fit your range

 

[T. Valko]

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]

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.