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:

COLUMN E	FORMULA COLUMN
A	1
B	2
C	3
E	1
D	2
G	3
H	4
I	5
Y	1
U	2
I	3

<tbody>
</tbody>

Instead of the following current issue:

COLUMN E	FORMULA COLUMN
A	1
B	2
C	3
E	4
D	5
G	6
H	7
I	8
Y	9
U	10
I	11

<tbody>
</tbody>

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)