Article updated on March 12, 2018

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

  1. Type 1 in cell B2 and then press Enter
  2. Right click on the black dot and drag down as far as needed.
  3. Click "Fill series"

Example 2

  1. Type 1 in cell A3 and 2 in cell A4
  2. Select A3 and A4
  3. 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:

=IF(B3=4,1,B3+1)

Copy cell B4 and paste to cells below as far as needed.

How to use the IF function

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:

=IF(B4="A",1,C3+1)

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:

=IF(TEXT(B4, "M-YYYY")=TEXT(B3, "M-YYYY"), C3+1, 1)

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:

=SUMPRODUCT(--(TEXT(B3,"YYYY-M")=TEXT($B$3:B3,"YYYY-M")))

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:

=SUMPRODUCT(--(YEAR(B3)=YEAR($B$3:B3)))

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:

=COUNTIF($B$3:B3,B3)

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:

=SUMPRODUCT(--(MATCH(B9, $C$3:$C$6, 1)=MATCH($B$9:B9, $C$3:$C$6, 1)))

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:

=SUMPRODUCT(--(YEAR($B$3:B3)&(C3:$C$3)=YEAR(B3)&C3))

Copy cell D3 and paste to cells below as far as needed.