1

I have a column filled with 0s and 1s like this:

A

1
1
1
0
0
1
0
1
1
0
0
1
0
0
1

What I want to do is simply have another column, with this values being counted until the next limit, like this:

A    B

1    1
1    2
1    3
0    1
0    2
1    1
0    1
1    1
1    2
0    1
0    2
1    1
0    1
0    2
1    1

I have searched for an answer, but I only found old posts and formulas that didn't work. This should be a simple thing. I only want one formula and I prefer to use only one column! I'm a noobie in Excel formulas.

Things I've tried but didn't work:

  1. Count duplicate values in a sequence
  2. Counting duplicates in Excel
  3. How do I count consecutive same values
  4. And other sources.
Fábio Linhares
  • 345
  • 5
  • 11

1 Answers1

1

steps as below

  1. write cell B1 with 1
  2. write cell B2 with =IF(A2=A1,B1+1,1)
  3. drag cell B2 down

enter image description here

PaichengWu
  • 2,649
  • 1
  • 14
  • 28