## 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.

Table of Contents Repeat values Repeat the range according to criteria in loop BatTodor asks: I failed to find right […]

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 […]

Find the longest/shortest consecutive sequence of a value

The array formula in cell D3 returns the the length of longest consecutive sequence of a value in column A. Cell […]

This array formula finds the sequence "Axis Chemical Co." (cell E2) and "Southern Railway" (cell E3) in two consecutive cells […]

Find a sequence of values – wildcard search

In my last post I showed you how to find a sequence of values. The array formula extracted the row […]

How to identify two consecutive dates in a list

Question: How to identify two consecutive dates in a list? Answer: Array formula in cell B1: =IFERROR(LARGE(IF((A1+1)=$A$1:$A$30,A1+1,""),1),"") How to enter […]

### 10 Responses to “Create number sequences”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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/

I have account numbers in COLUMN A, Status in COLUMN B, and dates mm/dd/yy in COLUMN C. I have multiple line items that apply to the same account number in COLUMN A. The Status for each line item can be 1, 2, 8-1, or 9-1 in COLUMN B and the date of each line item in COLUMN C. My goal is to have a sequence of 8-1's by date. I need to create a sequence of each orders 8-1's and a sequence of orders for 9-1's. That way when they sort on an order number (Account) they can get the sequence of updates. Ive tried Countif, Countifs, if(countif(, if(countifs... and I can get counts but they wont go in seq order of date. Ive tried to countif the date is <= date in CELL Cxx but no love.

Status Nomen

1 means created order.

8-1 means applied a change to order (can have multiple changes).

2 means order approved with updates.

9-1 means had a approved update after the approval (can have multiple updates after approval).

count Account_Number Status mm/dd/yy Seq needed

1 rt25hg62 1 1/1/17 1

2 dr87jh56 1 1/6/17 1

3 df12hg35 1 1/11/17 1

2 dr87jh56 8-1 1/26/17 1

4 er55tv16 1 2/15/17 1

1 rt25hg62 8-1 3/1/17 1

2 dr87jh56 2 3/2/17 1

3 df12hg35 8-1 3/12/17 1

1 rt25hg62 8-1 4/1/17 2

5 ww15hr26 1 4/3/17 1

5 ww15hr26 8-1 4/9/17 1

6 st45yu66 1 4/14/17 1

1 rt25hg62 8-1 4/15/17 3

4 er55tv16 8-1 4/17/17 1

2 dr87jh56 9-1 4/20/17 1

1 rt25hg62 2 4/22/17 1

3 df12hg35 8-1 4/27/17 2

3 df12hg35 8-1 5/1/17 3

3 df12hg35 2 5/5/17 1

4 er55tv16 8-1 5/7/17 2

6 st45yu66 8-1 5/8/17 1

4 er55tv16 8-1 5/9/17 3

1 rt25hg62 9-1 5/12/17 1

5 ww15hr26 8-1 5/16/17 2

3 df12hg35 9-1 5/18/17 1

1 rt25hg62 2 5/20/17 1

6 st45yu66 8-1 5/21/17 2

4 er55tv16 8-1 5/22/17 4

6 st45yu66 2 5/23/17 1

6 st45yu66 9-1 5/24/17 1

4 er55tv16 2 5/26/17 1

1 rt25hg62 9-1 5/27/17 2

4 er55tv16 9-1 6/1/17 1

5 ww15hr26 2 6/9/17 1