Create number sequences in excel 2007

Filed in Excel on Jan.24, 2010.

Email This article to a Friend

Create number sequences (Autofill)

Example 1

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

Example 2

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

Share and Enjoy: