# Sequential numbering formula omitting 0's and resetting after X rows

1. ## Sequential numbering formula omitting 0's and resetting after X rows

I have a series of data that is hundreds of rows. I have attached a sample file. In column E I want an "ID" that starts at 1 and sequentially numbers skipping any 0 values that are in column D. I was able to accomplish that with the below formula:

Formula:
`Please Login or Register  to view this content.`

The part that I haven't been able to figure out is that I want the numbering to "reset" after a specific number of rows. The number of rows will be specified by a cell. In my example file, cell J1 indicates that the count should reset after 15 rows. I have also included what the desired result should look like in column F.

Any help would be greatly appreciated.

2. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

Why don't you just use the change in inventory number to do this? Not sure quite what the significance of 15 is.

3. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

Thanks for the response! I would be ok using either the number OR the change in inventory number, but I am uncertain how to accomplish it that way either.

4. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

Great - glad I spotted it, because it makes it much easier!

=IF(D2=0,"",COUNTIFS(\$D\$2:\$D2,">0",\$A\$2:\$A2,A2))

5. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

I came up with the same as Ali, before seeing her response, i.e. in E2:

=IF(D2=0,"",COUNTIFS(\$D\$2:\$D2,">0",A\$2:A2,A2))

Copy down as required.

Pete

Snap, Pete!

7. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

Thank you very much. That worked perfectly!!

8. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

You're very welcome!

9. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

Agh!! I got 3 email notifications of Post #7 - hope the troubles are not starting up again.

Pete

10. ## Re: Sequential numbering formula omitting 0's and resetting after X rows

I suspect they might be, Pete ...

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1