Number rows but reset after a blank cell is encountered

[excel_2009]

Hi Excel gurus,

I have the following formula which assigns a number to a cell

=IF(E2="","",COUNTA($E$2:E2))

It works great however I have a small issue in relation to when a blank value is in a cell, I'd like for the numbers to reset to 1 and increment (instead of continuing to increment irresepctive of coming across a blank cell):

Desired result:

[TABLE="width: 500"]
<tbody>[TR]
[TD]COLUMN E
[/TD]
[TD]FORMULA COLUMN
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]G
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]I
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]U
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]I
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Instead of the following current issue:

[TABLE="width: 500"]
<tbody>[TR]
[TD]COLUMN E
[/TD]
[TD]FORMULA COLUMN
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]G
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]H
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]I
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Y
[/TD]
[TD]9
[/TD]
[/TR]
[TR]
[TD]U
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]I
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Please can someone help?

 

[L. Howard]

Try...

In F1 =IF(E1<>0,1,"")
In F2 and pull down =IFERROR(IF(E2<>0,F1+1,""),1)

Howard

 

[Snakehips]

Maybe..

Excel Workbook
	E	F	G
1	Header	*	Header
2	A	*	1
3	B	*	2
4	C	*	3
5	D	*	4
6	*	*	*
7	E	*	1
8	F	*	2
9	*	*	*
3

Hope that helps.

 

[excel_2009]

Hi Howard,

I've tried the formulas but the results are not as expected, there are instances of numbers being duplicated and the value does not reset when a blank is encountered? is it possible to use one formula and drag that down?

 

[excel_2009]

Perfect!!!! thank you so much!

 

[L. Howard]

Hi Howard,

I've tried the formulas but the results are not as expected, there are instances of numbers being duplicated and the value does not reset when a blank is encountered? is it possible to use one formula and drag that down?

Seems to work on my sheet.

Howard

Excel 2012
	E	F
1	1	1
2	q	2
3	3	3
4
5	5	1
6	x	2
7	7	3
8	8	4
9	9	5
10
11	11	1
12	w	2
13	13	3
14
15	q	1
16	w	2
17	w	3
18
19	q	1
20	w	2
21	e	3
Sheet4

Cell Formulas
Range	Formula
F1	=IF(E1<>0,1,"")
F2	=IFERROR(IF(E2<>0,F1+1,""),1)
F3	=IFERROR(IF(E3<>0,F2+1,""),1)
F4	=IFERROR(IF(E4<>0,F3+1,""),1)
F5	=IFERROR(IF(E5<>0,F4+1,""),1)
F6	=IFERROR(IF(E6<>0,F5+1,""),1)
F7	=IFERROR(IF(E7<>0,F6+1,""),1)
F8	=IFERROR(IF(E8<>0,F7+1,""),1)
F9	=IFERROR(IF(E9<>0,F8+1,""),1)
F10	=IFERROR(IF(E10<>0,F9+1,""),1)
F11	=IFERROR(IF(E11<>0,F10+1,""),1)
F12	=IFERROR(IF(E12<>0,F11+1,""),1)
F13	=IFERROR(IF(E13<>0,F12+1,""),1)
F14	=IFERROR(IF(E14<>0,F13+1,""),1)
F15	=IFERROR(IF(E15<>0,F14+1,""),1)
F16	=IFERROR(IF(E16<>0,F15+1,""),1)
F17	=IFERROR(IF(E17<>0,F16+1,""),1)
F18	=IFERROR(IF(E18<>0,F17+1,""),1)
F19	=IFERROR(IF(E19<>0,F18+1,""),1)
F20	=IFERROR(IF(E20<>0,F19+1,""),1)
F21	=IFERROR(IF(E21<>0,F20+1,""),1)