Count cells from Company to Company.

Question

Company&nbsp; 402 has 5 lines before Company 421. Want to enter the line count either in formula or Macro. Any help would be appreciated.&nbsp;&nbsp;Count1Company Number:&nbsp;&nbsp; 4025&nbsp;ABC MANUFACTURING CO., INC.5&nbsp;1 JONES RD SE5&nbsp;ATLANTA GA 303155&nbsp;Yes51Company Number:&nbsp;&nbsp; 4216&nbsp;JAMES, LLC6&nbsp;1 SWITZ6&nbsp;ST LOUIS MO 631476&nbsp;555/555-55556&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; No active products61Company Number:&nbsp;&nbsp; 4648&nbsp;THE Company8&nbsp;1821 LARKIN8&nbsp;MIDLAND MI&nbsp;8&nbsp;555/555-55558&nbsp;Yes8&nbsp;No active8&nbsp;&nbsp;81Company Number:&nbsp;&nbsp; 4025&nbsp;DEF MANUFACTURING CO., INC.5&nbsp;1 JONES RD SE5&nbsp;ATLANTA GA 303155&nbsp;Yes51Company Number:&nbsp;&nbsp; 402&nbsp;

sergeibaklan · Answer

Hi Matt,
&nbsp;
Starting from C3
=IF(ISBLANK(A3),C2+1,1)
and attached

deleted · Answer

Can I get the count cells to all be the total number of cells counted? instead of 1-5 it puts 5 in all of them, or 6 depending on how many rows are counted?

detlef_lewin · Answer

Matt,&nbsp;formula for helper column D:=IF(A2,SUM(D1,1),D1)Formula for column B:=COUNTIFS($D$2:$D$41,D2)&nbsp;&nbsp;

sergeibaklan · Answer

or
=IF(ISBLANK(A2),C1,IFNA(MATCH(FALSE,ISBLANK(OFFSET(A2,1,0,2000)),0),COUNTA(B2:B2000)))
but it depends on how the list of companies ended. Here I assume there are only blank cells after it.
&nbsp;

Forum Discussion

Count cells from Company to Company.

4 Replies

Resources

		Count
1	Company Number: 402	5
	ABC MANUFACTURING CO., INC.	5
	1 JONES RD SE	5
	ATLANTA GA 30315	5
	Yes	5
1	Company Number: 421	6
	JAMES, LLC	6
	1 SWITZ	6
	ST LOUIS MO 63147	6
	555/555-5555	6
	No active products	6
1	Company Number: 464	8
	THE Company	8
	1821 LARKIN	8
	MIDLAND MI	8
	555/555-5555	8
	Yes	8
	No active	8
		8
1	Company Number: 402	5
	DEF MANUFACTURING CO., INC.	5
	1 JONES RD SE	5
	ATLANTA GA 30315	5
	Yes	5
1	Company Number: 402