Choose formula that returns #value

hello all,

I have a formula someone on here provided me that works amazingly. It chooses out of 4 options based on the number of occurrences in a different cell and automatically adapts its count range as new data is entered (defined table). Now the only thing I need it to do is choose option 4 which is send off when 4 or more occurrences happen. As of now once it reaches its 5th occurence its just #value. What I need is basically; 1st = first time, 2nd = second time, 3rd = notify, 4th = send off, >5 = send off.

here is the formula;

=IF(OR(B7={"","end shift"}),"",CHOOSE(COUNTIF(B$2:INDEX(B:B,ROW()),INDEX(B:B,ROW())),"First Time","Second Time","Notify!","Send Off"))

thanks in advance!

Try:

Formula:

=IF(OR(B7={"","end shift"}),"",IFERROR(CHOOSE(COUNTIF(B$2:INDEX(B:B,ROW()),INDEX(B:B,ROW())),"First Time","Second Time","Notify!","Send Off"), "Send Off"))

You're welcome. Thanks for the rep.