COUNTIF reset formula

[John Brown]

Hello,

I was looking for some help on this one, I have looked on the net for a possible solution but cant seem to find someone that will fit with what I need.

I have a document to record the overtime process, this system works on that if you cancel overtime you get 2 warnings then on the 3rd strike its a 6 weeks ban. So far I've manage to design the sheet the way I want however I'm stuck at resetting the count after they have had 3 strikes to go back to the start and repeat the process.

This is my IF formula which works fine to highlight what stage
=IF(L105=1,"",IF(L105=2,"WARNING",IF(L105=3,"OVERTIME BAN",IF(L105=4,"",IF(L105=5,"WARNING",IF(L105=6,"OVERTIME BAN",""))))))

and this is the countif that links to it, I would like it to reset after its reaches the number 6 overtime ban back to begin the process again.
=IF(C105="","",COUNTIF($C$5:$C105,C105))

Any help would be fantastic thanks guys!

 

[vletm]

John Brown
Why do You need to 'reset'?
=IF(MOD(B3,4)=1,"",IF(MOD(B3,4)=2,"WARNING",IF(MOD(B3,4)=3,"OVERTIME BAN","")))
B3 is that 'cancel-value'.
Don't You ever need that 'cancel-value'?
If someone has once 'OVERTIME BAN' or someone has 11-times ... hmm?

 

[John Brown]

When they receive a ban it will activate a 6 week period, so If after that time if they incur an additional strike they go back to the first warning and through the same sequence of events until on the 3rd strike a new ban is activated.

 

[vletm]

John Brown
Did You test my formula?
No matter how many times someone has those 'cancel-values'.
1.="" 2.="Warning" 3.="Overtime Ban" 4.="" 5.="Warning" and so on.
999.= "Overtime Ban" ...
If You really need 'reset' something then You would need VBA.

 

[John Brown]

Great, all sorted now thanks for your help much appreciated.