Count Sequential Numbers

**AJRIGNEY** · 06-07-2012, 03:35 PM

Hello. I need either a formula or VBA that will count sequential numbers within a column of about 3,000. I would like the count of the sequence to be placed in the cell to the right of the last number in the sequence.

It should look something like this:

1
2
3
4
5 (5)

13
14
15
16 (4)

27
28
29
30
31
32
33(7)

etc.

I have attached a spreadsheet with the basic layout of 1,000 cells. Each new sequence is designated by color (Don't worry about the red, that's more for my understanding). If anyone could help, I would greatly appreciate it.

**tigeravatar** · 06-07-2012, 04:08 PM

AJRIGNEY,

Formula solution requires headers. Attached is a modified version of your posted example file.
In cell B2 and copied down:

Please Login or Register  to view this content.

**AJRIGNEY** · 06-07-2012, 04:23 PM

Leaving work right now, but I will plug in that formula tomorrow. Seems like it should work. Thanks so much!

**AJRIGNEY** · 06-08-2012, 08:38 AM

Quick Question. What do you mean by the formula solution requiring headers (Only been doing excel for about two weeks now)? I tried inserting the formula and adjusting it to my formatting, it messed up the numbers, changing the number by the number of rows I added. I shifted down everything by 9 cells, left the time in column A, but moved the count over to column I

**tigeravatar** · 06-08-2012, 10:34 AM

A header is a line of text in the row immediately above where the data starts that indicates what's in the column. So if your data starts in A9, then in A8 you should have a header labelled "Time" or "Timestamp", basically a short descriptive word or two for what the column contains. Then, in the column where the formula will be (for example if your data starts in A9, then the formula should go in B9), the cell immediately above the where the formula starts (so B8) should be a header for the formula, something like "Count" or "Consecutive Count", etc.

The example file i posted contains headers and the formula. You should be able to adapt what's in there to your data. If you are still having issues, please post a more accurate example file so I can show you how to apply the formula to it.