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:



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Header[/TD]
[TD]HT[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Position[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Position[/TD]
[TD]002[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Position[/TD]
[TD]003[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Header[/TD]
[TD]HT[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Position[/TD]
[TD]001[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Position[/TD]
[TD]002[/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD]...[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

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.