Create number sequences
Excel has a great built-in versatile tool for creating number sequences. In some situations, however, you need to rely on formulas. Read on to find many examples.
Watch a video where I demonstrate the techniques shown below
Create number sequences (Autofill)
The following two examples show you how to create a number sequence using two different techniques.
Example 1
- Type 1 in cell B2 and then press Enter
- Right click on the black dot and drag down as far as needed.
- Click "Fill series"
Example 2
- Type 1 in cell A3 and 2 in cell A4
- Select A3 and A4
- Left click and hold on the black dot and drag down as far as needed.
Create a repeating number sequence
In this example, I am going to create a repeating number sequence 1, 2, 3, 4.
This formula checks if the previous sequence number is 4, if true it restarts with value 1. If false it adds previous sequence number with 1.
Select cell B3 and type 1. Then press Enter.
Formula in B4:
Copy cell B4 and paste to cells below as far as needed.
Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.
Create a number sequence and restart when a cell value equals a criterion
In this example, the number sequence restarts every time the adjacent cell value in column B is equal to "A".
Select cell C3 and type 1. Then press Enter.
Formula in C4:
Copy cell C4 and paste to cells below as far as needed.
Create a number sequence to count records by year and month (sorted list)
This formula checks if the previous date has the same year and month as the current cell date. If true the previous sequence number is added by 1. If false the sequence starts all over again with 1.
The following formula will only work if the dates are sorted.
Formula in C4:
Copy cell C4 and paste to cells below as far as necessary.
Create a number sequence to count records based on year and month (unsorted list)
Array formula in B32:
Copy cell C3 and paste to cells below as far as necessary.
How to use the SUMPRODUCT function
The SUMPRODUCT function calculates the product of corresponding values and then returns the sum of each multiplication.
Create a number sequence to count dates based on year
This formula works fine with both sorted and unsorted lists. It simply counts the current cell year in the previous years cell range.
Formula in B39:
Copy cell C3 and paste to cells below as far as necessary.
Create a number sequence to count individual products
The COUNTIF function simply counts how many times the Item value has been displayed.
Formula in C3:
The first argument has a cell range that expands when you copy the cell to cells below.
Copy cell C3 and paste to cells below as far as necessary.
How to use the COUNTIF function
Counts the number of cells that meet a specific condition.
Create a number sequence to count prices within specific ranges
The formula in cell C9 creates a sequence depending on which range the price is in.
Formula in C9:
Copy cell C9 and paste to cells below as far as necessary.
Create a number sequence to count records by individual products and years
Formula in D3:
Copy cell D3 and paste to cells below as far as needed.
Recommended posts
Excel: How to automatically summarize preceding month and year
Question: How to automatically summarize preceding month or year? Answer: Formula to calculate the sum of last month: =IF(OR(MONTH(A3)>MONTH(A2),YEAR(A3)>YEAR(A2)), SUMPRODUCT(1*(MONTH($A$2:$A$9)=MONTH(A2))* […]
Find the longest/smallest consecutive sequence of a value [VBA]
This post Find the longest/smallest consecutive sequence of a value has a few really big array formulas. Today I would like to show […]
9 Responses to “Create number sequences”
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA code
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
I have tried using the "Create a number sequence to count individual products" formula but when I copy it down it moves the second $ to before the first number. I would like to know if there is anyway of stopping this from happening
Paul,
I don´t think I understand:
when I copy it down it moves the second $ to before the first number
=SUMPRODUCT(--(A52:$A$52=A52))
May be you want something like this
=COUNTIF($A$52:A52,A52)
Does (=SUMPRODUCT(--(TEXT($A$64:A64, "yyyy")&(B64:$B$64)=YEAR(A64)&B64)) + Enter) work for months
Erik Reppert,
What are you trying to calculate? Can you provide some example data?
in terms of the one of re-starting the sequence when a cell value equals a certain criterion, how about continuing the sequence by ignoring that same criterion?
so, in the case of A, B, D, D, C, B, D, A
the sequence will be if we ignore all the Ds: 1, 2, 0, 0, 3, 4, 0, 5
I'm trying to add to a hyphenated range. The sequence consists of two numbers in one cell for shipping labels, and it almost always has 40 added to each of the two numbers in it (because 40 small cartons fit in the larger shipping box).
Example: Cell B2 would read "MAB0053850-MAB0053889"
Cell B3 would read "MAB0053890-MAB0053929" and so on
The "C" column cells just have one sequence that's assigned to the larger shipping box, which contains the 40 "MAB..." cartons next to it.
Any way I can create a formula to extend these sequences vertically without splitting the two numbers into separate cells? Any help is appreciated. Thank you!
i have a serious problem..
i want to make a check voucher using excel 2007.. the problem is;i don't know how to series the check no. in the next voucher that follows.. in 1 sheet i format it into 2 voucher..
I made a template about “Insertion Sequence Numbers Automatic with Macro Codes”. You can add the sequence numbers to column that you want with your msgbox answering.
https://www.youtube.com/watch?v=hsQsW26nbNo
You can Download Here : https://netmerkez.wordpress.com/2015/08/23/insert-sequence-numbers-quickly-with-codes/