formula for criterium to count if value is given at least twice in 10 sequential cells

[FemkeBles]

Hi,

i'm struggeling with quite a complex issue (for me at least haha). I've a column with words 'wheel' and 'interval'. I want to count the number of times 'interval' is present in the column, but only if in 10 subsequential cells 'interval' is present 2 times. So not count interval in the case of "wheel-wheel-wheel-wheel-wheel-interval-wheel-wheel-wheel-wheel', but counting it in the case of 'wheel-interval-wheel-wheel-wheel-wheel-wheel-interval-wheel-wheel', as i need a threshold of when counting interval (It is about revolutions of a running wheel with mice, so one revolution does not count as 'activity', whereas two revolutions in a short time period do).

I really hope this makes a little bit sense and that somebody can help me!

Kind regards,
Femke

 

[bobsan42]

The logic is lost on me why an interval is connected with a revolution but it's irelevant
Your data should start below row 9. let's say first cell is A10.
Then you can find if the value in A10 is present in the 9 cells above or the 9 below using:

=COUNTIF(OFFSET($A10,-9,0,19,1),$A10)>1

Or you can use this one to only get TRUE/FALSE in case ther is interval:

=IF($A10="interval",COUNTIF(OFFSET($A10,-9,0,19,1),$A10)>1,"")