# Count how many sequence numbers

#### chriscorpion786

##### Board Regular
Hi All,

I need a formula to count the sequence of numbers from Product A to Product G, I have done it manually, but if I have thousands of rows, i need a formula to do this.

 Product A Product B Product C Product D Product E Product F product G Serial number sequence (Counted Max one) 125,666 125,667 15 556 557 558 559 4 450 666 667 668 669 670 888 5 333 566 475 123 987 100 9 0 100 101 102 103 104 105 106 7 98 99 100 99 98 31 32 3 56 57 88 87 10 11 12 3

### Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

#### jasonb75

##### Well-known Member
This should do it
Book2
ABCDEFGH
1Product AProduct BProduct CProduct DProduct EProduct Fproduct GMax Sequence
212566612566715556557558559
4
3450666667668669670888
5
43335664751239871009
0
5100101102103104105106
7
6989910099983132
3
756578887101112
3
Sheet4
Cell Formulas
RangeFormula
H2:H7H2=IFERROR(1/(1/MAX(FREQUENCY(IF((A2:F2+1)=B2:G2,COLUMN(A2:F2)),IF((A2:F2+1)<>B2:G2,COLUMN(A2:F2)))))+1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

#### chriscorpion786

##### Board Regular
Thanks, but could you be kind enough to explain a bit...how the formula works..

#### jasonb75

##### Well-known Member
The first 'IF' creates an array of column numbers (column A =1, column B =2, etc) where the number in any given column is 1 greater than the number in the previous column. The second 'IF' returns the column numbers that are not in the first array by using a not equal logical test instead of an equal test. These arrays are used to create the data array and bins array for the frequency function (see excel help for more on this function).

Max returns the largest bin from the array, which is the count of the longest sequence of consecutive numbers. Due to the way that the formula works, the result will be 1 less than the actual count, so 1 is added at the end, but because a result of 1 would be incorrect when there are no consecutive sequence numbers, division by 0 is used to create an error and eliminate these rows before the 1 is added and the final result is shown in the cell.

#### chriscorpion786

##### Board Regular
Thank you Jason...very clear and have learnt a few ideas now...