Create number sequences (Autofill)

Example 1

  1. Type "1" in cell B3 + Enter
  2. Right click on black dot and drag down as far as needed. (See arrow)

  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 on black dot and drag down as far as needed. (See arrow)

Create a repeating number sequence

In this example I am going to create a repeating number sequence 1, 2, 3, 4.

Formula in A9:

=IF(A8=4, 1, A8+1) + Enter

copied down as far as needed.

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 equals "D".

Formula in B17:

=IF(A17="D", 1, B16+1) + Enter

copied down as far as needed.

Create a number sequence to count records by year and month (sorted list)

Formula in B27:

=IF(AND(YEAR(A27)=YEAR(A26), MONTH(A27)=MONTH(A26)), B26+1, 1) + ENTER

copied down as far as needed.

Alternative formula:

=IF(TEXT(A26, "myyyy")=MONTH(A27)&YEAR(A27), C26+1, 1) + ENTER

copied down as far as needed.

Create a number sequence to count records by year and month (unsorted list)

Array formula in  B32:

=SUM(IF(TEXT($A$32:A32, "myyyy")=MONTH(A32)&YEAR(A32), 1, 0)) + Ctrl + Shift + Enter

copied down as far as needed.

Alternative formula in C32:

=SUMPRODUCT(--(TEXT($A$32:A32, "myyyy")=MONTH(A32)&YEAR(A32))) + Enter

copied down as far as needed.

Create a number sequence to count records by year (sorted list)

Formula in B39:

=IF((TEXT(A39, "yyyy"))*1=YEAR(A38), B38+1, 1) + Enter

copied down as far as needed.

Create a number sequence to count records by year (unsorted list)

Formula in B46:

=SUMPRODUCT(--(TEXT($A$46:A46, "yyyy")*1=YEAR(A46))) + Enter

copied down as far as needed.

Create a number sequence to count individual products

Formula in B52:

=SUMPRODUCT(--(A52:$A$52=A52)) + Enter

copied down as far as needed.

Create a number sequence to count records within specific price ranges

Price ranges (0-500, 501-1000, 1001-1500)

Formula in B58:

=SUM(--(MATCH(A58, {0, 501, 1001, 1501}, 1)=MATCH(A58:$A$58, {0, 501, 1001, 1501}, 1)))

+ Ctrl + Shift + Enter copied down as far as needed.

Create a number sequence to count records by individual products and years

Formula in C64:

=SUMPRODUCT(--(TEXT($A$64:A64, "yyyy")&(B64:$B$64)=YEAR(A64)&B64)) + Enter

copied down as far as needed.

Related posts:

Create unique distinct year and months from a long date listing in excel

Create a list of dates with blanks between quarters in excel

Create a monthly date range in excel

Create a quartely date range in excel

Create a custom date range in excel