Continuously numbering (conditional) with reset

[jules_05]

Hi all,

first of all, I am looking forward to a productive cooperation with all of you guys It's my first post, so let's get it up and running!

My problem is the following:
I would need a continuously, conditional numbering with a reset function. Let me give you guys an example for a better understanding:

	A	B
1	Header	HT
2	Position	001
3	Position	002
4	Position	003
5	Header	HT
6	Position	001
7	Position	002
n	...	...

<tbody>
</tbody>

In column A I have the text 'Header' or 'Position'. In column B I have 'HT' with every cell in column A which contains the text 'Header' - so far so good. What I wanna do is, create a formula which reads the text in column A and give me a continuous numbering with the condition to start over when there is the text 'Header' in column A.
So something like this: $B1=IF($A1='Header','HT',......count & reset if column A='Header')

I hope you guys understood my problem and useful answers, tips and hints are very much appreciated!

Thanks in advance,
Jules

 

[jimrward]

try the following, I am sure someone will come up with a better solution

I inserted a row of headers to make the following work and inserted the following in B2

=IF(A2="Header","HT",MAX(--(A2="Header"),B1)+1)

I then formatted the column with custom format of 000, as converting the result with TEXT(..., throws the formula into returning 1 on each row

 

[Shrikant]

=IF(A2="Header","HT",MAX(0,B1)+1)

will suffice.

 

[jules_05]

=IF(A2="Header","HT",MAX(0,B1)+1)

will suffice.

Unfortunately this gives me the following:


I've entered the following formula for cell AN3 (see the screenshot) and copied it down...

=IF($AM3="Header","HT",MAX($AN2,0)+1))

Did I do anything wrong?

 

[J.Ty.]

Hi,

I suggest the following:

In the first row you enter "HT" by hand.
Below you use the formula (here for C2)

 =IF(A2="Header","HT",IF(C1="HT",1,1+C1))

J.Ty.