# How to count consecutive numbers based on the first number of the column

• Hi

Looking for a formula to count the consecutive numbers in 1 column based on the first Number/Column.

Then if the first number in the column is Zero the result is shown as a negative number but if the first number in the column is 1 the result is shown as a positive number.

If you can help it would be greatly appreciated.

Here is an example.

[TABLE="width: 145"]

[tr]

[TD="width: 29"][TABLE="width: 168"]

[tr]

[TD="width: 52"]Result [/TD]
[TD="class: gmail-xl67, width: 29"]-1[/TD]
[TD="class: gmail-xl67, width: 29"]-1[/TD]
[TD="class: gmail-xl67, width: 29"]3[/TD]
[TD="class: gmail-xl67, width: 29"]2[/TD]

[/tr]

[tr]

[TD="class: gmail-xl63"] [/TD]
[TD="class: gmail-xl64"]A[/TD]
[TD="class: gmail-xl64"]B[/TD]
[TD="class: gmail-xl64"]C[/TD]
[TD="class: gmail-xl64"]D[/TD]

[/tr]

[tr]

[TD="class: gmail-xl63"] [/TD]
[TD="class: gmail-xl65"] [/TD]
[TD="class: gmail-xl65"] [/TD]
[TD="class: gmail-xl65"] [/TD]
[TD="class: gmail-xl65"] [/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]2[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]3[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]4[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]5[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]6[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]7[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]8[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]9[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]10[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]11[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]12[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]13[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]14[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]15[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]16[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]0[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]17[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]18[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]19[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"]20[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]
[TD="class: gmail-xl67"]1[/TD]

[/tr]

[tr]

[TD="class: gmail-xl66"] THX[/TD]
[TD="class: gmail-xl68"]

[/TD]
[TD="class: gmail-xl68"] [/TD]
[TD="class: gmail-xl68"] [/TD]
[TD="class: gmail-xl68"] [/TD]

[/tr]

[/TABLE]
[/TD]
[TD="class: gmail-xl67, width: 29"][/TD]
[TD="class: gmail-xl67, width: 29"][/TD]
[TD="class: gmail-xl67, width: 29"][/TD]
[TD="class: gmail-xl67, width: 29"][/TD]

[/tr]

[/TABLE]

• Re: How to count consecutive numbers based on the first number of the column

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

• Re: How to count consecutive numbers based on the first number of the column

Sure. Let's look at column A as each Column will have it's own result. The starting point is to look at row 1 first. This will determine if the result will be expressed as a positive number or a negative number. Zero is negative and 1 is positive. In column A the first number is negative making the result expressed as a negative number, we then look at row 2. Row 2 is a 1. This means that based on the first row the Zero was followed by a 1 and the formula end as simply one consecutive negative number giving the result of -1

If you look at column C the first row number is 1 making the result a positive number of 1. you then look at row 2 and you still have a 1 bringing the total to 2. Then row 3 has another 1 with a new total of 3 but then when you look at row 4 you have a zero so the total consecutive numbers for this column would be 3 as the zero in row 4 stops the consecutive number count.

I use this to track consecutive days up or down for stocks. You can think of the rows(1 threw 20) as days. 1 would be today, 2 yesterday and so on. What I want to know is based on today how many consecutive days were up (positive number) or down (negative numbers)

I hope this makes sense. Thank you for the reply.

• Re: How to count consecutive numbers based on the first number of the column

[cp]*[/cp]

Ali

Enthusiastic self-taught user of MS Excel who's always learning!
If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.