Looking for an Excel Formula to Count the Number of Filled Cell Since the Last Blank Cell

[kabutocat]

As titled, I'm looking for a formula in column B that would do something like this:

David	1
John	2
Rhys	1
Emma	1
Shepard	2
Margaret	3
Elise	4

I don't mind needing an extra column for the formula to work.

Thanks in advance.

 

[Legacy 143009]

Assuming your data starts from A2, paste to B2:

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

 

[bebo021999]

If data starts from A1
In B1:

=IF(A1="","",IFERROR(ROW()-LOOKUP(2,1/($A$1:A1=""),ROW($A$1:A1)),ROW()))

 

[Peter_SSs]

23 01 18.xlsm
	A	B
1	Name	Count
2	David	1
3	John	2
4
5
6	Rhys	1
7
8	Emma	1
9	Shepard	2
10	Margaret	3
11	Elise	4
Count Group

Cell Formulas
Range	Formula
B2:B11	B2	=IF(A2="","",N(B1)+1)

 

[Legacy 143009]

23 01 18.xlsm
	A	B
1	Name	Count
2	David	1
3	John	2
4
5
6	Rhys	1
7
8	Emma	1
9	Shepard	2
10	Margaret	3
11	Elise	4
Count Group

Cell Formulas
Range	Formula
B2:B11	B2	=IF(A2="","",N(B1)+1)

I wasn't aware that there is a function called N

 

[Peter_SSs]

I wasn't aware that there is a function called N

There you go. Now you do know.

If data starts from A1
In B1:

=IF(A1="","",IFERROR(ROW()-LOOKUP(2,1/($A$1:A1=""),ROW($A$1:A1)),ROW()))

Problem could be ... if a new row is then subsequently inserted at the top of the sheet.

 

[Peter_SSs]

@kabutocat
I notice that you have marked post #3 as the solution, which suggests that perhaps your data does start in row 1, unlike my assumption (& Flashbond's).

Just making sure that you are aware that ..
a) If a row is subsequently added at the top (for example, to add headings) then the formula will return incorrect results - see second mini sheet below.
b) A shorter and more robust option is available - see column C

23 01 18.xlsm
	A	B	C
1	David	1	1
2	John	2	2
3
4	Rhys	1	1
5
6	Emma	1	1
7	Shepard	2	2
8	Margaret	3	3
9	Elise	4	4
Count Group (2)

Cell Formulas
Range	Formula
B1:B9	B1	=IF(A1="","",IFERROR(ROW()-LOOKUP(2,1/($A$1:A1=""),ROW($A$1:A1)),ROW()))
C1:C9	C1	=IF(A1="","",N(IFERROR(OFFSET(C1,-1,),0))+1)

After new header row is inserted

23 01 18.xlsm
	A	B	C
1	Name	Num	Num
2	David	2	1
3	John	3	2
4
5	Rhys	1	1
6
7	Emma	1	1
8	Shepard	2	2
9	Margaret	3	3
10	Elise	4	4
Count Group (2)

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="","",IFERROR(ROW()-LOOKUP(2,1/($A$2:A2=""),ROW($A$2:A2)),ROW()))
C2:C10	C2	=IF(A2="","",N(IFERROR(OFFSET(C2,-1,),0))+1)

 

[kabutocat]

@kabutocat
I notice that you have marked post #3 as the solution, which suggests that perhaps your data does start in row 1, unlike my assumption (& Flashbond's).

Just making sure that you are aware that ..
a) If a row is subsequently added at the top (for example, to add headings) then the formula will return incorrect results - see second mini sheet below.
b) A shorter and more robust option is available - see column C

23 01 18.xlsm
	A	B	C
1	David	1	1
2	John	2	2
3
4	Rhys	1	1
5
6	Emma	1	1
7	Shepard	2	2
8	Margaret	3	3
9	Elise	4	4
Count Group (2)

Cell Formulas
Range	Formula
B1:B9	B1	=IF(A1="","",IFERROR(ROW()-LOOKUP(2,1/($A$1:A1=""),ROW($A$1:A1)),ROW()))
C1:C9	C1	=IF(A1="","",N(IFERROR(OFFSET(C1,-1,),0))+1)

After new header row is inserted

23 01 18.xlsm
	A	B	C
1	Name	Num	Num
2	David	2	1
3	John	3	2
4
5	Rhys	1	1
6
7	Emma	1	1
8	Shepard	2	2
9	Margaret	3	3
10	Elise	4	4
Count Group (2)

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="","",IFERROR(ROW()-LOOKUP(2,1/($A$2:A2=""),ROW($A$2:A2)),ROW()))
C2:C10	C2	=IF(A2="","",N(IFERROR(OFFSET(C2,-1,),0))+1)

Thank you Peter for the solution.
I didn't clarify in my original post (as I forgot to mention), I was looking for a formula that doesn't reference column B itself.
I am working with named tables, and for some reason it doesn't like when formula's reference cells in their own column (it doesn't auto update as normal when adding new rows), hence why I chose post #3.

 

[Peter_SSs]

Thanks for the further information.

I am working with named tables,

Do you mean a formal Excel table (ListObject) like below?

kabutocat.xlsm
	A	B	C	D
1	Name	Num	Num2
2	David	2	1
3	John	3	2
4
5	Rhys	1	1
6
7	Emma	1	1
8	Shepard	2	2
9	Margaret	3	3
10	Elise	4	4
11
12
Count Group (2)

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="","",IFERROR(ROW()-LOOKUP(2,1/($A$2:A2=""),ROW($A$2:A2)),ROW()))
C2:C10	C2	=IF(A2="","",N(OFFSET(C2,-1,))+1)

 

[kabutocat]

Thanks for the further information.


Do you mean a formal Excel table (ListObject) like below?

kabutocat.xlsm
	A	B	C	D
1	Name	Num	Num2
2	David	2	1
3	John	3	2
4
5	Rhys	1	1
6
7	Emma	1	1
8	Shepard	2	2
9	Margaret	3	3
10	Elise	4	4
11
12
Count Group (2)

Cell Formulas
Range	Formula
B2:B10	B2	=IF(A2="","",IFERROR(ROW()-LOOKUP(2,1/($A$2:A2=""),ROW($A$2:A2)),ROW()))
C2:C10	C2	=IF(A2="","",N(OFFSET(C2,-1,))+1)

Yes. For my spreadsheet I require formula's to autopopulate when adding a new row.