Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
min function on non consecutive rows | Excel Worksheet Functions | |||
copy non-consecutive rows | Excel Discussion (Misc queries) | |||
PULLING OUT CONSECUTIVE ROWS | Excel Discussion (Misc queries) | |||
Inserting multiple non-consecutive rows | Excel Discussion (Misc queries) | |||
Select non-consecutive rows | Excel Discussion (Misc queries) |