#### bryanworkman

##### New Member

- Joined
- Oct 29, 2015

- Messages
- 6

I have racked my brain with a solution for this problem and can never seem to crack it. I have to calculate Cost Per Head (CPH) for several different slices of a data set consisting of >500k rows. This is simplified, but suppose my data has a column for Month, Team, State, Account, Account Type, and the Amount. The Amount will either be the Total Cost for that particular intersection of Month/Team/State/Account or it will be the Total Salaried or Hourly Employees for that same intersection, determined by the Account Type column (Cost or Employee). See the table below.

Month Team State Account Account Type Amount

Jan A AZ Salaries Cost 50000

Jan A AZ Benefits Cost 12500

Jan A AZ Payroll Taxes Cost 20625

Jan A AZ Salaried Employees Employee 5

Jan A AZ Hourly Employees Employee 1

Jan A NY Salaries Cost 120000

Jan A NY Benefits Cost 30000

Jan A NY Payroll Taxes Cost 52500

Jan A NY Salaried Employees Employee 10

Jan A NY Hourly Employees Employee 0

Jan B CA Salaries Cost 80000

Jan B CA Benefits Cost 20000

Jan B CA Payroll Taxes Cost 35000

Jan B CA Salaried Employees Employee 2

Jan B CA Hourly Employees Employee 10

Jan B NY Salaries Cost 170000

Jan B NY Benefits Cost 42500

Jan B NY Payroll Taxes Cost 63750

Jan B NY Salaries Employees Employee 4

Jan B NY Hourly Employees Employee 20

I can calculate the CPH several different ways. I can create Sumifs formulas to pull all the Cost and all the Employees for whatever slice I want and then do the simple division. I can also create a Pivot Table to filter down to the Cost and Employees and then do the simple division formula off of the Pivot Table. However, I need to explore sooooo many different Accounts/Teams/Months/etc. that it is not feasible to set up so many Sumifs and the Pivot Table Method using a side formula just doesn't work as I explore different slices and the location of all the items in the Pivot Table changes.

My ideal solution would be a Pivot Table that allows me to roll up or slice the data any way I want and be able to see columns for the Total Cost, Total Employees, and the CPH. See the table below:

Month Jan

Team All

State NY

Account Total Cost Total Employees CPH

Salaries 290000 34 8529

Benefits 72500 34 2132

Payroll Taxes 116250 34 3419

Grand Total 478750 34 14081

I am pretty comfortable with Calculated Fields, Calculated Items, and VBA so any solutions are welcome!

Month Team State Account Account Type Amount

Jan A AZ Salaries Cost 50000

Jan A AZ Benefits Cost 12500

Jan A AZ Payroll Taxes Cost 20625

Jan A AZ Salaried Employees Employee 5

Jan A AZ Hourly Employees Employee 1

Jan A NY Salaries Cost 120000

Jan A NY Benefits Cost 30000

Jan A NY Payroll Taxes Cost 52500

Jan A NY Salaried Employees Employee 10

Jan A NY Hourly Employees Employee 0

Jan B CA Salaries Cost 80000

Jan B CA Benefits Cost 20000

Jan B CA Payroll Taxes Cost 35000

Jan B CA Salaried Employees Employee 2

Jan B CA Hourly Employees Employee 10

Jan B NY Salaries Cost 170000

Jan B NY Benefits Cost 42500

Jan B NY Payroll Taxes Cost 63750

Jan B NY Salaries Employees Employee 4

Jan B NY Hourly Employees Employee 20

I can calculate the CPH several different ways. I can create Sumifs formulas to pull all the Cost and all the Employees for whatever slice I want and then do the simple division. I can also create a Pivot Table to filter down to the Cost and Employees and then do the simple division formula off of the Pivot Table. However, I need to explore sooooo many different Accounts/Teams/Months/etc. that it is not feasible to set up so many Sumifs and the Pivot Table Method using a side formula just doesn't work as I explore different slices and the location of all the items in the Pivot Table changes.

My ideal solution would be a Pivot Table that allows me to roll up or slice the data any way I want and be able to see columns for the Total Cost, Total Employees, and the CPH. See the table below:

Month Jan

Team All

State NY

Account Total Cost Total Employees CPH

Salaries 290000 34 8529

Benefits 72500 34 2132

Payroll Taxes 116250 34 3419

Grand Total 478750 34 14081

I am pretty comfortable with Calculated Fields, Calculated Items, and VBA so any solutions are welcome!

Last edited: