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!

HI

I have attached a simpler way of doing things using an array formula, so you do not need columns B,C and D. The formula can be copied and pasted but if you make any changes you will need to use ctrl-shift-enter to ensure it functions correctly.

By having the values you are checking kept separately they can be changed without affecting the formula itself.

Regards

Jeff

It's been a few days and this topic has gone to the next page.
Just bumping because I really wanted to see if what I am trying to do is even possible.