Reset Counter

Hello,

In Column A, I have this list of 1's and empty cells*, i would like to have
in column B a function that gives me the next result:

*A1=1 B=1
*A2=1 B=2
*A3=1 B=3
*A4="" B=""
*A5=1 B=1
*A6=1 B=2
*A7="" B=""
*A8=1 B=1
*A9=1 B=2
*A10=1 B=3

It would be like a counter that tells me the number of times 1 has appeared
so far, but when it reaches: "" it restarts and goes on again.

,thanks

In B1 put

=IF(A1<>1,"",1)

In B2 put

=IF(A2<>1,"",1+IF(B1="",0,B1))

and formula drag that down as far as you need.

--

Originally Posted by Ed

Hello,

In Column A, I have this list of 1's and empty cells*, i would like to have
in column B a function that gives me the next result:

*A1=1 B=1
*A2=1 B=2
*A3=1 B=3
*A4="" B=""
*A5=1 B=1
*A6=1 B=2
*A7="" B=""
*A8=1 B=1
*A9=1 B=2
*A10=1 B=3

It would be like a counter that tells me the number of times 1 has appeared
so far, but when it reaches: "" it restarts and goes on again.

,thanks

In B1:
=IF(A1="",0,1)
In B2:
=IF(A2="",0,B1+1)
Copy down as far as you need

Format Custom as "General;General;" to suppress zeros

--
Kind regards,

Niek Otten

"Ed" <[email protected]> wrote in message news:[email protected]...
| Hello,
|
| In Column A, I have this list of 1's and empty cells*, i would like to have
| in column B a function that gives me the next result:
|
| *A1=1 B=1
| *A2=1 B=2
| *A3=1 B=3
| *A4="" B=""
| *A5=1 B=1
| *A6=1 B=2
| *A7="" B=""
| *A8=1 B=1
| *A9=1 B=2
| *A10=1 B=3
|
| It would be like a counter that tells me the number of times 1 has appeared
| so far, but when it reaches: "" it restarts and goes on again.
|
| ,thanks

Hello again, both formulas work fine, but I forgot to mention a small detail
when I was explaining the problem the first time, sorry about that... Both
formulas are referenced to the upper cell, so when I delete a row i get #REF!
as a result until the next blank cell. Do you have any suggestions to
overcome that problem? The file in which I am working with is meant to have
posibility to delete rows once in a while.

thanks again, really!

Ed