Create number sequences in excel 2007

Popular categories

Automate

Charts

Compare

Conditional formatting

Excel ads

Dashboard reporting with excel This e-book teaches you how to create your own Excel dashboard reports, starting from scratch. Learn how to create mini-charts, how to use Excel's Camera tool, how to set up Excel databases, and a lot more.

Random Data Generator for Microsoft Excel 2003-2010. Create random fake data sets for testing charts -- export to MDB, XLS, TXT, CSV, XML

Excel Worksheet-Formula Add-Ins allows worksheet formulas to change chart settings. For the first time ever, your formulas can create traffic-light charts, highlight chart elements, assign number formats, and much more.

Ads - Web hosting

Web Hosting for only 1 Penny!

FatCow Web Hosting. Free Trial , Free Setup, Free Support – All risk free!

Top Rated Web Hosting - $3.95/mo

100% Eco Friendly Web Hosting Service -- iPage Web Hosting

Ads - Antivirus

Kaspersky Internet Security 2014

Get Digital Help > Excel > misc > Create number sequences in excel 2007

← Previous post -

Filed in Excel, misc 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.

Comments (7)

7 Responses to “Create number sequences in excel 2007”

Paul says:

February 19, 2013 at 9:37 am

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

Reply
- Oscar says:
  
  February 19, 2013 at 2:24 pm
  
  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))
  
  Reply
- Kamran Mumtaz says:
  
  February 19, 2013 at 5:15 pm
  
  May be you want something like this
  
  =COUNTIF($A$52:A52,A52)
  
  Reply
Erik Reppert says:

March 25, 2013 at 11:33 pm

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

Reply
- Oscar says:
  
  March 27, 2013 at 8:44 am
  
  Erik Reppert,
  
  What are you trying to calculate? Can you provide some example data?
  
  Reply
brian bunyan says:

July 26, 2013 at 4:35 pm

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

Reply
Andy says:

January 10, 2014 at 9:49 pm

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!

Reply