Counting Consecutive Cells

[MrT_FortyFour]

Good Afternoon All,

I have a large excel data sheet and one of the calculated cells indicates if a tank level is less than 85% and if it is, returns a "1" value. We have a process safety issue if that tank remains less than 85% for more than 1 hour. I'm trying to calculate how many times the trigger is >=10 consecutively. I've been able to indicate that I've had months where this has happened by using the following formula [=IF(OR(COUNTIFS($A$3:$A$93868,"=7",$B$3:$B$93868,"=2019",$W$3:$W$93868,"=1")>=10),"T","F")]. What I would like to do is return a number throughout the range (W3:W93868) of how many times we've had 10 or more consecutive events.

I'm not sure if I'm explaining it properly but I uploaded a screenshot of the excel sheet to show the column I'm trying to count.

Thanks for any help.

Bruce

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this example with a small data sample helps - count how many times in A2:A30 there are 5 (value in C2) or more consecutives 1

Book1
	A	B	C	D
1	Numbers		Consecutives	Result
2	1		5	3
3	1
4	0
5	1
6	1
7	1
8	1
9	1
10	1
11	0
12	1
13	1
14	1
15	1
16	1
17	0
18	0
19	1
20	1
21	1
22	1
23	0
24	1
25	1
26	1
27	1
28	1
29	1
30	0
Plan2

Cell Formulas
Range	Formula
D2	D2	{=SUM(--(FREQUENCY(IF(A2:A30=1,ROW(A2:A30)),IF(A2:A30<>1,ROW(A2:A30)))>=C2))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

Hope this helps

M.

 

[MrT_FortyFour]

Thank you Marcelo. Works perfectly. Appreciate the help.

 

[Marcelo Branco]

You are welcome. Glad to help.

M.