number increment skipping blanks

[brandyw]

Hi,
I have data coming into A1:A20000.
In the B column, data is checked and marked L if criteria are met.
In the C column I want to number consecutively, rows were B column is marked L.
Example:

x......A…….B.....C
1…..123
2…..123
3……52 …..L…..1
4…..123
5…..123
6…..123
7……52…..L…...2
8…..123
9……52…..L…...3
10….52…..L…...4

I can get C column to return 1 easy enough, but how do I increment the numbers by 1, skipping the blank rows?
I could use Max+1 but I restart the count daily (about 1000 rows),
I manually restart the count with 1 and would like the following numbers to increment by 1, if possible.

Thank you, Brandy

[Paul]

Hi brandyw,

This will do the counts for you, put this in C1:

=IF(B1="","",COUNTIF($B$1:B1,"L"))

How you plan to restart the numbering I'm not so sure, since placing a 1 into any cell in column C will overwrite the formula. You could, of course, change the formula as needed from $B$1 to the cell next to where you put a 1 in column C. So if you restart with 1 in C891, in B891 simply change the formula to
=IF(B891="","",COUNTIF($B$891:B891,"L"))

[brandyw]

Hi Paul,
Thank you for your reply,
I have tried a few of those types of formulas, but they require an adjustment every day.
There’s a formula out there that counts consecutively while skipping blanks.
I came across it a few months ago while researching for another excel formula, except I didn’t mark the page and of course now that I can use it I can’t find it.
I didn’t look at it very long and just can’t remember the approach it took.
At least I get to learn more about Excel, while searching.
I would restart the count by putting in a 1 over the formula, on the first instance for a new day of data, eventually having the formula check for new day and restart count automatically.

Thank you again for your quick response,
Brandy

[deadlyduck]

Not sure if this is what you want but it might give you an idea of what might work:

In cell C1 only:

Please Login or Register  to view this content.

In cell c2 (and copied to subsequent cells):

Please Login or Register  to view this content.