# Counting consecutive Zeros

#### RandyD123

##### Board Regular
I am trying to count consecutive Zeros on each row. This would have to exclude "RDO". My range starts at F3. The count would be on today's date in column "LP". All rows have data validation that only allows whole numbers 1-10 in quarter hour increments or RDO. If any cell in the row gets a number the count would have to start over. Once the count reaches 14 consecutive zero's the name would highlight in yellow. If the count gets broken after 14 by a number then the count starts over. I have a sheet posted here. I'm not sure if this would be a macro or a formula. Any help would be much appreciated. Thanks

#### RandyD123

##### Board Regular
I have corrected the formula.
Excel Formula:
``=IF(OR(INDEX(\$F3:\$LO3,LP\$1)=0,INDEX(\$F3:\$LO3,LP\$1)="RDO"),MAX(IF(COUNTIF(OFFSET(INDEX(\$F3:\$LO3,LP\$1),0,0,1,0-ROW(INDIRECT("\$1:\$"&LP\$1))),0)+COUNTIF(OFFSET(INDEX(\$F3:\$LO3,LP\$1),0,0,1,0-ROW(INDIRECT("\$1:\$"&LP\$1))),"RDO")=ROW(INDIRECT("\$1:\$"&LP\$1)),ROW(INDIRECT("\$1:\$"&LP\$1))-COUNTIF(OFFSET(INDEX(\$F3:\$LO3,LP\$1),0,0,1,0-ROW(INDIRECT("\$1:\$"&LP\$1))),"RDO"),"")),0)``
Ok that seems to work but it leaves me with a couple of questions. If for example lets say that Sunday 8/15 gets skipped, I have it set to turn that cell yellow. But if I go back today and just fill in Sunday, but not Monday, LP doesn't count anything. I guess I can live with that just not sure why it does that.

Is the "helper cell" still needed and what exactly is the info that it is showing? (Mon01/02)?

Finally, if any cell in LP hits 14 or more, the entire corresponding name cell has to highlight in yellow. Once that number drops below 14 the name cell will lose the highlight. I think that might be a conditional formatting thing, just not sure how to do that.

I can't thank you enough for all the help so far. This is so far out of my skills so this is awesome that you are taking your time to help me.

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### RandyD123

##### Board Regular
Ok that seems to work but it leaves me with a couple of questions. If for example lets say that Sunday 8/15 gets skipped, I have it set to turn that cell yellow. But if I go back today and just fill in Sunday, but not Monday, LP doesn't count anything. I guess I can live with that just not sure why it does that.

Is the "helper cell" still needed and what exactly is the info that it is showing? (Mon01/02)?

Finally, if any cell in LP hits 14 or more, the entire corresponding name cell has to highlight in yellow. Once that number drops below 14 the name cell will lose the highlight. I think that might be a conditional formatting thing, just not sure how to do that.

I can't thank you enough for all the help so far. This is so far out of my skills so this is awesome that you are taking your time to help me.
So I see that the "helper cell" is needed. Just not sure why it references the "E" column? And why it shows a (random date?) with no spaces? Obviously I am fine with a helper cell just curious why it wouldn't reference the "F" column? I see that when I make it reference the F column the date changes to Sun01/01....

Also I noticed if I back the date up to 8/8 in F3, nothing gets counted until all cells prior to todays date get a number or an RDO. Which is fine. I think I like that. The fact that the cells highlight in yellow is a reminder that no blank cell are allowed!!!

Last edited:

#### RandyD123

##### Board Regular
So I see that the "helper cell" is needed. Just not sure why it references the "E" column? And why it shows a (random date?) with no spaces? Obviously I am fine with a helper cell just curious why it wouldn't reference the "F" column? I see that when I make it reference the F column the date changes to Sun01/01....

Also I noticed if I back the date up to 8/8 in F3, nothing gets counted until all cells prior to todays date get a number or an RDO. Which is fine. I think I like that. The fact that the cells highlight in yellow is a reminder that no blank cell are allowed!!!
So I figured out the conditional formatting for the name column. The only thing left is the "helper cell" explanation, why it references the "E" column and not the "F" column. And why it's formatted the way it is?

Replies
1
Views
333
Replies
2
Views
148
Replies
12
Views
542
Replies
2
Views
207
Replies
9
Views
294

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,115
Messages
5,768,204
Members
425,459
Latest member
Danniey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back