Countifs with multiple criteria and ranges within single column?

Hi all,

Having some trouble with this one.

I have a large set of data, all within one column (roughly 100k lines). And I am trying to come up with a formula to see if a specific cell value exists between two other specific cell values in that column. Whether or not it returns a true, yes, a number, or whatever is of little importance however.

My problem is since I have multiple criteria all within the same column and the range in between each instance is different, it's a bit of a puzzle to me. Anyone have an idea?

See attachment for an example (check both tabs).

Thank you,

Edit: I noticed in the mock formula I had in there I listed F2, it should of said F3.

Edited again: Please see post below with updated information