Count occurances of random number groups

[NKB]

Hi Everyone....Can anyone please suggest a formula in Excel 2003 which can be used to count the number of occurances of random number groups in a column? A group is classed as 1 or more numbers that occur consecutively. An example of the data (each number in an individual cell) is;

2, 2, 5, 5, 3, 3, 2, 2, 1, 5, 5, 5, 5, 2, 1, 1, 3, 3....

The above shown as groups would be; 2 (2's), 2 (5's), 2 (3's), 2 (2's), 1 (1), 4 (5's), 1 (2), 2 (1's), 2 (3's).

The result of the above would be;

Occurance of 2 no. 2 = 2
Occurance of 2 no.5 = 1
Occurance of 2 no. 3 = 2
Occurance of 1 no. 1 = 1
occurance of 4 no. 5 = 1
Occurance of 2 no. 1 = 1

Any assistance or suggestions will be appreciated.

 

[Domenic]

To count number of times 4 consecutive 5's occur, assuming that A2:A100 contains the data, C2 contains 4, and D2 contains 5, try...

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

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

 

[NKB]

Works perfectly; many thanks for the formula.