Count of consecutive runs

[tigerzen]

I'm after a way of counting the number of consecutive non-zero "hits". The spreadsheet should show what I'm after but essentially we need to show how many times there was 1 hit, how many times there were 2 hits etc. To the right of the data are the actual numbers of consecutive hits.

Book13
	A	B	C	D	E	F	G
1	Hits	Month
2	3	Jan-20			Streaks
3	1	Feb-20		1	2	3	4
4	0	Mar-20		1	2	1	2
5	2	Apr-20
6	0	May-20
7	1	Jun-20
8	3	Jul-20
9	0	Aug-20
10	1	Sep-20
11	1	Oct-20
12	3	Nov-20
13	0	Dec-20
14	4	Jan-21
15	3	Feb-21
16	2	Mar-21
17	2	Apr-21
18	0	May-21
19	3	Jun-21
20	2	Jul-21
21	3	Aug-21
22	2	Sep-21
23	1	Oct-21
24	0	Nov-21
Sheet1

 

[kevin9999]

I only count 1 streak of 4 consecutive non-zero "hits" (Jan-21 to Apr-21) not 2 as your expected result shows. If that is correct ( 1 not 2 ) then the following might be what you're looking for? This may do until a more elegant solution arrives

Book3
	A	B	C	D	E	F	G	H
1	Hits	Month
2	3	Jan-20			Streaks
3	1	Feb-20		1	2	3	4	5
4	0	Mar-20		1	2	1	1	1
5	2	Apr-20
6	0	May-20
7	1	Jun-20
8	3	Jul-20
9	0	Aug-20
10	1	Sep-20
11	1	Oct-20
12	3	Nov-20
13	0	Dec-20
14	4	Jan-21
15	3	Feb-21
16	2	Mar-21
17	2	Apr-21
18	0	May-21
19	3	Jun-21
20	2	Jul-21
21	3	Aug-21
22	2	Sep-21
23	1	Oct-21
24	0	Nov-21
Sheet1

Cell Formulas
Range	Formula
D4:H4	D4	=SUM(--(FREQUENCY( IF($A$2:$A$24>0, ROW($A$2:$A$24)), IF($A$2:$A$24<=0, ROW( $A$2:$A$24))) =D3))

 

[tigerzen]

Thanks Kevin, that works beautifully. I'd be lying if I said I understood the formula completely but I'll break it down to try and get a better understanding of it.

 

[kevin9999]

Glad I could help, and thanks for the feedback