Increase Number when Value changes in another Column in Excel

Suppose you have a list of Values in Column A, and now you want to increase number by 1 in Column B when the Value in Column A changes, which means the numbers in Column B increase until the Value in Column A Changes, then the number increment starts from 1 again as shown in below image.

To increase numbers based on another column when value changes, use the following formula. Follow below steps to apply the formula-

Formula 1

  1. Enter the Formula =IF(A2<>A1,1,B1+1) into a blank cell which beside your data i.e. “B2”.
    See the below Screenshot for reference-
  1. Then drag the fill handle down to the cells that you want to fill sequence numbers. Sequence will restart for every changes in Column A, see below image-

Formula 2

  1. Enter the Formula =COUNTIF($A$2:A2,A2) into a blank cell which beside your data i.e. “B2”.
    See the below Screenshot for reference-
  1. Then drag the fill handle down to the cells that you want to fill sequence numbers. Sequence will restart for every changes in Column A, see below image-

Leave Comment

Your email address will not be published. Required fields are marked *