Note: The other languages of the website are Google-translated. Back to English
English English

How to auto number a column based on cell value on another column in Excel?

doc-number-column-based-on-value-1
For instance, you have a range of data in Excel, and you want to number column A based on the values in column B as below screenshot shown. Now this tutorial will tell you a formula to solve this task in Excel.
Auto number a column based on values on another column

arrow blue right bubble Auto number a column based on values on another column

Fortunately, there is a formula can quickly number a column based on values of anther column in Excel.

1. Type 1 into the first cell of the column you want to number. See screenshot:

doc-number-column-based-on-value-2

2. In the second cell of the column you want to number, type this formula =IF(B1:B13="Total","",COUNTIF($A$1:A1,">0")+1), and press Enter key.

doc-number-column-based-on-value-3

Tip: In the above formula, B1:B13 is the column range you want to number based on, and A1 is the cell you typed 1 into, and “Total” is the value you do not want to number.

3. Then drag the autofill handle to the column range you want to number. See screenshot:

doc-number-column-based-on-value-4


Relative Articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (11)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How do i autonumber based on a value but in my case there is no "TOTAL". This is how my data is: UNKNOWN UNKNOWN UNKNOWN SEG034-PI-012.038 SEG034-PI-012.039 SEG034-PI-012.038 SEG034-PI-012.038 SEG034-PI-012.038 SEG034-PI-001.019 SEG034-PI-001.019 UNKNOWN UNKNOWN SEG034-PI-001.019 SEG034-PI-001.019 SEG034-PI-001.019 SEG034-PI-001.021 SEG034-PI-001.021 SEG034-PI-001.021 UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN UNKNOWN CT0034.0202.004 CT0034.0202.004 So i basically want to put number (ID), if the value repeats itll get the same value if it already has assigned (in the example data unknown repeats randomly). Please help.
This comment was minimized by the moderator on the site
Did you ever get an answer to this? I have the same question.
This comment was minimized by the moderator on the site
Hi, I have similar question. I have a alphanumeric values of Column A, some times repeating some time unique.. So Base on the numerical values available in the Column A, I want the a serial number auto assigned in Column B as shown below.Can any help please?

COLUMN# A COLUMN# BWO/20-21/001 001-01WO/20-21/001 001-02
WO/20-21/001 001-03
WO/20-21/002 002-01
WO/20-21/002 002-02
WO/20-21/002 002-03
WO/20-21/002 002-04
WO/20-21/003 003-01


This comment was minimized by the moderator on the site
Hi there,
Hope this works. =IF(A3="";"";IF(IFERROR(VLOOKUP(A3;$A$2:B2;2;FALSE);"N/A")="N/A";MAX($B$2:B2+1);VLOOKUP(A3;$A$2:B2;2;FALSE)))
REMEMBER TO MAKE YOUR FIRST CELL ex. "B1", 1 THEN BELOW IT ADD THE FORMULA ex. "B2".


This comment was minimized by the moderator on the site
Yo necesito hacer lo mismo que este ejemplo, pero en mi caso es requerido que me de un consecutivo segun un numero X en una columna, pero que si me varia el numero X en esa columna, me de un consecutivo diferente ej...

A B
1 1
2 1
3 1
1 2
2 2

Algo asi....
This comment was minimized by the moderator on the site
In cell A3 paste the following

=IF(B3=B2,"",COUNTIF($A$2:A2,">0")+1)
This comment was minimized by the moderator on the site
This formula works but my first number based on the criteria is starting with 2 and then 3,4,5 onwards. How can I get it to start at 1?
This comment was minimized by the moderator on the site
Hi, you can change the last argument 1 in the formula to 2, like =IF(B1:B13="Total","",COUNTIF($A$1:A1,">0")+2).
This comment was minimized by the moderator on the site
Sales data and applying a sequential job number that grows by 1 for each entry?As sales data is entered into a table, at the time of the client acceptance a date stamp is applied to the relevant cell in that rowAcceptance is random and the row (client) to be next accepted is also random.
I have a Column of cells (Column A) partially populated by a numerical date range that increases as per the date
Without sorting the table.
How do I write a formula that will calculate a sequential whole number starting at 1 in the corresponding cellegcell A32020 Nov 15th 13:17:50 (time stamp)(equates to) 44150.5540549768
A10 next entry 2020 Nov 15th 11:48:16 (time stamp)44150.4918552083
A13 next entry
2020 Nov 15th 11:48:26 (time stamp)
44150.4919619213
etc
In the corresponding cellsC3 needs to return a value of 3C10 needs to return a value of 1C13 a value of 2etcgrowing by 1 each time
Any ideas?
This comment was minimized by the moderator on the site
How do i take set values and add different varying values to it for projection.
This comment was minimized by the moderator on the site
Hi Sir,

When Cell 1 is Number this FT20240001 and Next need to auto calculate FT20240002 ( FT20240001 +1) in If function.

Thanks
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations