# Count occurances of random number groups

#### NKB

##### New Member
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.

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Domenic

##### MrExcel MVP
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

##### New Member
Works perfectly; many thanks for the formula.

Replies
9
Views
141
Replies
2
Views
67
Replies
9
Views
336
Replies
1
Views
238
Replies
1
Views
140

1,187,175
Messages
5,962,039
Members
438,578
Latest member
MrJimC

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back