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.

 

[Peter_SSs]

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

Not sure what mechanism you are using to add a new row to the table, but if I select a cell in the bottom row of the table, right-click and choose Insert - Table row below
I get an inconsistent copy down (& resultant incorrect cell value) with the longer formula in column B and a correct copy down of the short formula in column C

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	-1	4
11
12
13
Count Group (2)

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

If I then fill in cell A11 above both columns now return correct results, but the inconsistent formulas remain in column B - which would be a concern to me.

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	Tom	5	5
12
13
Count Group (2)

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

 

[kabutocat]

Not sure what mechanism you are using to add a new row to the table, but if I select a cell in the bottom row of the table, right-click and choose Insert - Table row below
I get an inconsistent copy down (& resultant incorrect cell value) with the longer formula in column B and a correct copy down of the short formula in column C

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	-1	4
11
12
13
Count Group (2)

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

View attachment 83267

If I then fill in cell A11 above both columns now return correct results, but the inconsistent formulas remain in column B - which would be a concern to me.

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	Tom	5	5
12
13
Count Group (2)

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

That is interesting. I wasn't aware that adding rows differently can affect how excel populates formula's
I was adding rows by selecting an entire row and then right clicking the row number and then insert.

I started a new worksheet and used your inputs and added rows how I usually do. Both solutions worked normally and didn't have any inconsistent formula's.

However, when I'm on my main spreadsheet I was working on, I get inconsistent formula's adding rows both ways. I'm not sure why that is other than it only happens on coloumns that have formula's referencing their own columns.

 

[Peter_SSs]

What you are describing is making me wonder about whether you actually have a 'formal' table (ListObject) despite your 'Yes' answer in post 10.
.. although, are you generally adding a row at the bottom of the table or in the middle somewhere?


If you go to the Formulas ribbon tab and click 'Name Manager', do you get something like this that links a table name to the data on your worksheet?

 

[kabutocat]

What you are describing is making me wonder about whether you actually have a 'formal' table (ListObject) despite your 'Yes' answer in post 10.
.. although, are you generally adding a row at the bottom of the table or in the middle somewhere?


If you go to the Formulas ribbon tab and click 'Name Manager', do you get something like this that links a table name to the data on your worksheet?

View attachment 83273

I normally add rows in the middle of the table.

Yes I do get something like what's pictured in the name manager

 

[Peter_SSs]

I normally add rows in the middle of the table.

Yes I do get something like what's pictured in the name manager

Then I am not able to reproduce an error as you describe for the formula suggested in column C of posts 9 & 10.