How do I count the number of sequential recurrences?

I have an excel file which I have performed an operation on. I now have a column that is populated by either the value 1 or 0.

What do I need to do to count the number of "1's" that appear in a consecutive sequence.

My array looks like this:
1
0
0
0
1
1
1
1
1
0
1
1
0

What I need to be able to extract is that we saw a run of 5 1's then a run of 2 1's. How do I do that?

4 Answers

This is not elegant, but it will work:

After your column of '1's and '0's,

make your 2nd column:

0
=(A2+B1)*A2 <-- You can just drag-fill this downward
=(A3+B2)*A3
=(A4+B3)*A4
.
.
.

...and make the 3rd column:

0
=SUMIF(B3,0,B2) <-- You can just drag-fill this downward as well
=SUMIF(B4,0,B3)
=SUMIF(B5,0,B4)
.
.
.

The 3rd column will show the number of '1's in each group, with the sum beside the last '1' in the run.

Using the COUNTIF function you can tell excel to count the occurrences of a specific criteria in a given range.

COUNTIF(range,criteria)

Lets say your data is in column A and rows 1 through 13, you would use the following to count the number of times "1" occurs.

=COUNTIF(A1:A13, 1)

Adjust the range to the range you want to count and the criteria to what you want to count.

I had a similar problem. My approach was ugly and inefficient, but it worked.

I made a second column which looked to see if the adjacent cell value was what I was looking for, and if so, give a number of 0+the value of the cell above it in the second column.

I made a third column where each cell looked at the adjacent cell in column 2, and if the value was greater than the one below it, to give a value of 0+ the max value in the third column up to the row of the cell. If not, it spit out an empty value ("") In this way I was able to put an sequential identifier number.

I made a fourth column where which looked at the adjacent cell in column 3, and if it was any value except blank, to put the value of that row from column 2.

I then made a fifth column which began with 1, and every row was one higher than the previous row, up to the max value from Column 3.

I finished by making a sixth column which used the lookup function. The lookup value would be the adjacent cell in column 5. The Range would be column 3 and 4. I would have it spit out the value from column 4.

I define a sequence of identical values as a run. Each number on column 5 represents a run. Each number in column 6 represents the the size of that run. This allows you to generate the sizes of each of the runs.

Like I said, this is an ugly way to go about this, and could probably be written a lot cleaner in VBA, but it is effective.

Don't think of it as counting consecutive numbers. Think of it as finding the position of the first 0.

=match(0,A1:A14,0)-1

Drag down the formula, letting the range adjust as you drag down. It will give you the number of 1's you have in a row at any given point in the data.