Skip to main content

How to increment numbers when value changes in another column?

doc increment value changes 1

Supposing, you have a list of values in column A, and now you want to increment number by 1 in column B when the value in column A changes, which means the numbers in column B increment until the value in column A changes, then the number increment starts from 1 again as left screenshot shown. In Excel, you can solve this job with following method.

Increment numbers when value changes in each group with formula

arrow blue right bubble Increment numbers when value changes in each group with formula

To increment numbers based on another column when value changes, the following simple formula may help you.

1. Enter this formula: =IF(A2<>A1,1,B1+1) into a blank cell which beside your data, B2, for instance, see screenshot:

doc increment value changes 2

2. Then drag the fill handle down to the cells that you want to fill sequence numbers and restart for every change in column A, see screenshot:

doc increment value changes 3

Note: Here is another formula =COUNTIF($A$2:A2,A2) also can help you to create a number sequence that restarts for every change in another column.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (15)
No ratings yet. Be the first to rate!
deep05
deep05
This comment was minimized by the moderator on the site
Hi i have a scenerio like:-

Code Price Result
2 100 1
2 250 2
2 300 3
4 600 1
4 800 2
5 900 1
5 1000 2
5 1000 2
6 300 1

how would i get values that i want in result column
Reply
Report
0
0
skyyang
skyyang     deep05
This comment was minimized by the moderator on the site
Hello,
I'm sorry, I didn't understand your question. Could you describe it in more detail?
Thank you!
Reply
Report
0
0
Letminthang
Letminthang
This comment was minimized by the moderator on the site
Hi,
Not sure if this is he best place to ask this but would love your help on this formula. I deposit an amount say $300 in my bank and get interest say $1.5 per day on the given amount. Once the accumulated interest reaches $100 (in a certain no. of days) I reinvest it and deposit it back into the bank which then increases my interest to $2.5 per day and so on. I would like the increasing interest, no. of days taken for each cycle amount accumulated and total amount invested in a table. The part I'm facing difficulty is in the part where the interest increases in each row and also want to constrain it to reinvest it if above $100 only, and also the amount of days it took for me to go above $100 each time.
Reply
0
0
Francesco
Francesco
This comment was minimized by the moderator on the site
Io devo fare questo:
In una casella ho un numero che va da 0 a 100 e vorrei che in un altra casella che ha valore iniziale 0 aumenti di una unità quando nella prima casella si passa da 0 a 5 e poi ogni 10. Quindi da 5 a 15 e l'sltra casella passa da 10 a 11, da 15 a 20 e l'altra casella passa da 11 a 12 così via.
Reply
0
0
Sherrise
Sherrise
This comment was minimized by the moderator on the site
I have number in a column. I want to count how many times that there is a break in the numbering sequence. So if you have 2,3,4, 10, 13,14,15. You would get a count of 3 times there was a break between the sequence in the numbers. I have spent two days trying all different kinds of formulas. Please help.
Reply
0
0
Apurva
Apurva
This comment was minimized by the moderator on the site
How to make this arrayformula? 
Reply
0
0
Inez
Inez
This comment was minimized by the moderator on the site
Hey! I have to imput steps from 1 to 15 and then the time used for that step is displayed in another collumn but then I want this time to stay the same when I change te step number. How do i make a collumn stay the same so I can imput other data?
Thanks!
Reply
0
0
Evelyne
Evelyne
This comment was minimized by the moderator on the site
If i have a list like this
BananaBananaBananaOrangeOrangeCherryCherryBananaOrange
How do i make it keep increment the sequence number in function of the name, i want it to recognized that he already seen this word/Number before in the column. do i have to always put a sorting filter to make it work?
thanks,
Evelyne
Reply
0
0
Mace
Mace     Evelyne
This comment was minimized by the moderator on the site
If you use the other suggested formula, =COUNTIF($A$2:A2,A2), Excel keeps counting no matter the order the words are in.
Reply
0
0
hrishkesh.pattepu
hrishkesh.pattepu
This comment was minimized by the moderator on the site
Hello Is there a way to calculate orange as total 6 and not show the increment order like 1,2,3,4,5 but at the end of row as 6 before entering into next new name
Reply
Report
0
0
Ali Khan
Ali Khan
This comment was minimized by the moderator on the site
i need to increment number using the duplicate criteria, means if the criteria changes the Main Serial Number and Sub Serial Number should change, for example
1.1 604
1.2 604
2.1 605
2.2 605
2.3 605
3.1 606
3.2 606
4.1 607
4.2 607

hope you got my point. kindly help
Reply
0
0
josefin
josefin
This comment was minimized by the moderator on the site
is there a way to do this but I want to separate the different numbers, for example like
A1 1
A2 1
A3 2
A4 3
A5 3

I would like to automatically calulate the maximum number 1 in a row, maximum number 2 etc
Reply
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations