Insert Formulas in list of data

[kalbercht]

Hi - I have a list of records summarizing individuals by cost center (there's also other fields of data but these are the 2 main ones that I'm concerned about).

I need to be able to insert a row at each change in cost center and then insert a formula to count the number of individuals within that cost center. (So all of the rows above - up to the point of a different cost center).



I need to be able to physically insert a formula and the the count value as data may change. The formulas need to be able to recalculate the count of inviduals.


The data looks like this:

Doe Jane CstCtr1
Evans Joe CstCtr1
Jones Kris CstCtr1
Abrams Mack CstCtr2
Evans Mary CstCtr2


I need it to look like this:



Doe Jane CstCtr1
Evans Joe CstCtr1
Jones Kris CstCtr1

Count 3 <---this needs to be a formula


Abrams Mack CstCtr2
Evans Mary CstCtr2

Count 2 <---this needs to be a formula



Many thanks.

 

[grizz]

If your only counting cstctrl
=COUNTIF(B3:B200,"cstctrl1")
=COUNTIF(B3:B200,"cstctrl2")

 

[Peter_SSs]

Welcome to the MrExcel board!

Have a look at Excel's built-in Subtotal feature (I assume your columns have headings). It will insert the rows and the formulas for you. If you need more detailed help with *how* to do it, post back and tell us what Excel version you are using.

 

[kalbercht]

Thanks for the replies. I'm on Excel 2007. I need to use macros to insert the formulas and not the subtotal function because I will need to adjust it to insert additional subtotals.


I'm imagining that somehow I need to figure out the range of the records within a cost center, determine the last row and insert the formula, and then determine the range of the next cost center, etc.... until all cost centers are reviewed.


I can probably do this but it would take me some time.

Thanks again!

 

[Peter_SSs]

Thanks for the replies. I'm on Excel 2007. I need to use macros to insert the formulas and not the subtotal function because I will need to adjust it to insert additional subtotals.

If the suggestion below does not solve your problem, then expanding on what you mean by this may help us to understand.

I'm imagining that somehow I need to figure out the range of the records within a cost center, determine the last row and insert the formula, and then determine the range of the next cost center, etc.... until all cost centers are reviewed.

You shouldn't need to do this - the Subtotal feature does it for you.

Assuming the data has headings and starts in A1 like this:

Excel Workbook
	A	B
1	Name	Cost Centre
2	Doe Jane	CstCtr1
3	Evans Joe	CstCtr1
4	Jones Kris	CstCtr1
5	Abrams Mack	CstCtr2
6	Evans Mary	CstCtr2
Sheet1

Then run this macro (or do it manually)

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Cost_Centres()<br>    Range("A1").Subtotal GroupBy:=2, Function:=xlCount, TotalList:=Array(2), _<br>        Replace:=True, PageBreaks:=False, SummaryBelowData:=<SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


This is the result:

Excel Workbook
	A	B
1	Name	Cost Centre
2	Doe Jane	CstCtr1
3	Evans Joe	CstCtr1
4	Jones Kris	CstCtr1
5	CstCtr1 Count	3
6	Abrams Mack	CstCtr2
7	Evans Mary	CstCtr2
8	CstCtr2 Count	2
9	Grand Count	5
Sheet1