Count Repeated Group For Specefic No

[Excel777]

Hey Experts

i'm searching for way to count consecutive group of specefic number

as shown here in this example

Excel Workbook
	A	B	C	D	E	F
1			No	Repeated Group Of 1	Repeated Group Of 2	Repeated Group Of 3
2	1		0	2	2	2
3	1		1	1	3	2
4	0
5	1
6	1
7	1
8	0
9	0
10	1
11	1
12	1
13	0
14	0
15	0
16	1
17	1
18	0
19	0
20	1
21	1
22	0
23	1
24	0
25	0
26	0
Sheet2

thank in advance for help

 

[T. Valko]

Hey Experts

i'm searching for way to count consecutive group of specefic number

as shown here in this example

Excel Workbook
	A	B	C	D	E	F
1			No	Repeated Group Of 1	Repeated Group Of 2	Repeated Group Of 3
2	1		0	2	2	2
3	1		1	1	3	2
4	0
5	1
6	1
7	1
8	0
9	0
10	1
11	1
12	1
13	0
14	0
15	0
16	1
17	1
18	0
19	0
20	1
21	1
22	0
23	1
24	0
25	0
26	0
Sheet2

thank in advance for help

As your column headers in D1:F1 just use the numbers 1, 2 and 3.

Enter this array formula** in D2:

=SUM(IF(FREQUENCY(IF($A$2:$A$26=$C2,ROW($A$2:$A$26)),IF($A$2:$A$26<>$C2,ROW($A$2:$A$26)))=D$1,1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to F2 then down to D3:F3.

 

[Excel777]

100 Million Of thanks T.Valko Works Great It saved my time

 

[T. Valko]

100 Million Of thanks T.Valko Works Great It saved my time

You're welcome. Thanks for the feedback!

 

[Excel777]

For T. Valko

​

 

[T. Valko]

For T. Valko

Wow!

Thank you!