# Sequence Number Required

##### Active Member
Respected All,

I need a Formula that can allow me to generate the sequence number against the Qty if sum of the qty <=20
i had tried using =CEILING(SUM(A\$2:A2),20)/20 formula but couldn't reach to the required result

 Qty Desire Sequence Number| Formula Used Remarks 2 =CEILING(SUM(A\$2:A2),20)/20 OK 1 1 OK 3 1 OK 5 1 OK 2 1 OK 5 1 OK 9 2 OK 2 2 OK 5 2 OK 4 2 OK 2 2 Wrong Result|Sum of the Qty in Sequence Number have Increased from 20 1 3 OK 3 3 OK 1 3 OK 1 3 OK 4 3 OK 2 3 OK 2 3 OK 2 3 OK 5 4 OK 1 4 OK 5 4 OK 5 4 OK 4 4 OK 2 4 Wrong Result|Sum of the Qty in Sequence Number have Increased from 20 4 5 OK 2 5 OK 3 5 OK

regards
Naresh

#### Attachments

• 12.1 KB Views: 6

##### Member
Is this formula meets your requirements?
=IF(SUM(\$A\$2:A2)<=20,SUBTOTAL(3,\$A\$2:A2),"")

##### Active Member

thanks for your effort, but couldn't reach to the required result
i want the formula that generate the same sequence number until the sum value <=20
and then next sequence number should change and remain same till sum value reach <=20 and so on..

##### Member
Check the expected result with helper column.

#### Attachments

• 9.8 KB Views: 3

#### p45cal

##### Well-Known Member
Without a helper column:
In B2 enter the number 1
In B3:
Code:
``=IF((SUMIF(B\$2:B2,B2, A\$2:A2)+A3)>20,MAX(B\$2:B2)+1,B2)``
copied down.