Identify missing values

Hello again,

I have a new project I am working on and in it, I am trying to get Excel to identify to me if a certain value hasn't occurred in the previous X attempts (5 in the example sheet attached).

Another example is...say Johnny hasn't been to school in the last 5 days, it will flag on the 6th day that Johnny wasn't present in the last 5 days.

I attempted to do this, but could only think of sumproduct function where
=sumproduct(--($B2:$B6="A"))
=sumproduct(--($B2:$B6="B"))
=sumproduct(--($B2:$B6="C"))
are placed in consecutive columns...and a value of 0 would indicate that the particular value hasn't occurred in the previous 5 cells.

I suspect that there is a more space conscious method of achieving this...any ideas?

Thanks!