Formula to Subtotal Employee Hours/Rate based on Job Position for Billing Summary

[JenMcG]

Hello,

I’m looking for help building a formula that will subtotal total hours worked AND subtotal the billing rate based on the employee's position:

Job position is located in column C (range 12 to 5000)
Positions include:
Project Manager
Site Supervisor
General Labour/Demo
Fire/Water Technician
Safety Officer
Administrator

Total hours are in column I (range 12 to 5000)

Subtotal Billing Rate is in column M (range 12 to 5000)

There are multiple entries per day (like a timesheet) that need to be subtotaled by position.

Thank you,

 

[alansidman]

Cannot manipulate data in a picture. Please reload the file data using XL2BB. See my signature for instructions.

 

[JenMcG]

Hello,

I’m looking for help building a formula that will subtotal total hours worked AND subtotal the billing rate based on the employee's position:

Job position is located in column C (range 12 to 5000)
Positions include:
Project Manager
Site Supervisor
General Labour/Demo
Fire/Water Technician
Safety Officer
Administrator

Total hours are in column I (range 12 to 5000)

Subtotal Billing Rate is in column M (range 12 to 5000)

There are multiple entries per day (like a timesheet) that need to be subtotaled by position.

Thank you,

Cannot manipulate data in a picture. Please reload the file data using XL2BB. See my signature for instructions.

Hello Alansidman,

Unfortuatnly I do not have that addin and can't add it in myself as its a work computer that is protected.

 

[alansidman]

Then perhaps you can upload a sample to a location like Box.Net or Dropbox.com. Then provide link. Still need to be able to manipulate data without having to recreate your sample. Help us to help you.

 

[JenMcG]

Then perhaps you can upload a sample to a location like Box.Net or Dropbox.com. Then provide link. Still need to be able to manipulate data without having to recreate your sample. Help us to help you.

NEW Large Loss T&M Billing Summary.xlsx

Shared with Dropbox

www.dropbox.com

Try this please AND Thank you

 

[awoohaw]

I needed to delete all the calculations under the last record. But, does this work for you? you can copy the last row's formulas down as you add records

NEW Large Loss T&M Billing Summary.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1					Start Date	End Date		Position	Subtotal Hours	Subtotal Billing Rate
2		Job Number	E-23-1234	Billing Period	######	######		Project Manager	3.00	240.00
3		Business Name						Site Supervisor	16.00	140.00
4		Loss Location						General Labour/Demo	0.00	0.00
5		City / Province / Postal Code						Fire/Water Technician	0.00	0.00
6		Claim #						Safety Officer	0.00	0.00
7		Adjuster Name						Administrator	7.00	60.00
8		Examiner Name
9
10
11
12	Line #	Employee Name	Role	Date Worked (MM/DD/YY)	Job Number	Job Code	Start Time	End Time	Total Hours	RT/OT	RT/OT Cal (Helper Column)	Rate	Subtotal
13	4	Sam Smith	Administrator	6-Feb-23	E-23-1234		8:30 AM	12:00 PM	3.50	RT	1	$ 30.00	$ 105.00
14	7	Sam Smith	Administrator	5-Feb-23	E-23-1234		8:30 AM	12:00 PM	3.50	RT	1	$ 30.00	$ 105.00
15	1	Jen McGinnis	Project Manager	6-Feb-23	E-23-1234		1:30 PM	2:30 PM	1.00	RT	1	$ 80.00	$ 80.00
16	3	Jen McGinnis	Project Manager	6-Feb-23	E-23-1234		3:30 PM	4:30 PM	1.00	RT	1	$ 80.00	$ 80.00
17	6	Jen McGinnis	Project Manager	5-Feb-23	E-23-1234		1:30 PM	2:30 PM	1.00	RT	1	$ 80.00	$ 80.00
18	2	Travis McGinnis	Site Supervisor	6-Feb-23	E-23-1234		8:00 AM	4:00 PM	8.00	RT	1	$ 70.00	$ 560.00
19	5	Travis McGinnis	Site Supervisor	5-Feb-23	E-23-1234		8:00 AM	4:00 PM	8.00	RT	1	$ 70.00	$ 560.00
20	8				E-23-1234				0.00
Labour Hours Entry

Cell Formulas
Range	Formula
I2:I7	I2	=SUMPRODUCT((--($H2=$C$13:$C$5000))*($I$13:$I$5000))
J2:J7	J2	=SUMPRODUCT((--($H2=$C$13:$C$5000))*($L$13:$L$5000))
K13:K19	K13	=VLOOKUP(J13,$O$3:$P$4,2,FALSE)
L13:L19	L13	=VLOOKUP(C13,$Q$3:$R$12,2,FALSE)
M13:M19	M13	=(I13*K13)*L13
E13:E20	E13	=$C$2
I13:I20	I13	=(H13-G13)*24

Cells with Data Validation
Cell	Allow	Criteria
J13:J20	List	=$O$3:$O$4
C13:C20	List	=$Q$3:$Q$12

 

[JenMcG]

I needed to delete all the calculations under the last record. But, does this work for you? you can copy the last row's formulas down as you add records

NEW Large Loss T&M Billing Summary.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1					Start Date	End Date		Position	Subtotal Hours	Subtotal Billing Rate
2		Job Number	E-23-1234	Billing Period	######	######		Project Manager	3.00	240.00
3		Business Name						Site Supervisor	16.00	140.00
4		Loss Location						General Labour/Demo	0.00	0.00
5		City / Province / Postal Code						Fire/Water Technician	0.00	0.00
6		Claim #						Safety Officer	0.00	0.00
7		Adjuster Name						Administrator	7.00	60.00
8		Examiner Name
9
10
11
12	Line #	Employee Name	Role	Date Worked (MM/DD/YY)	Job Number	Job Code	Start Time	End Time	Total Hours	RT/OT	RT/OT Cal (Helper Column)	Rate	Subtotal
13	4	Sam Smith	Administrator	6-Feb-23	E-23-1234		8:30 AM	12:00 PM	3.50	RT	1	$ 30.00	$ 105.00
14	7	Sam Smith	Administrator	5-Feb-23	E-23-1234		8:30 AM	12:00 PM	3.50	RT	1	$ 30.00	$ 105.00
15	1	Jen McGinnis	Project Manager	6-Feb-23	E-23-1234		1:30 PM	2:30 PM	1.00	RT	1	$ 80.00	$ 80.00
16	3	Jen McGinnis	Project Manager	6-Feb-23	E-23-1234		3:30 PM	4:30 PM	1.00	RT	1	$ 80.00	$ 80.00
17	6	Jen McGinnis	Project Manager	5-Feb-23	E-23-1234		1:30 PM	2:30 PM	1.00	RT	1	$ 80.00	$ 80.00
18	2	Travis McGinnis	Site Supervisor	6-Feb-23	E-23-1234		8:00 AM	4:00 PM	8.00	RT	1	$ 70.00	$ 560.00
19	5	Travis McGinnis	Site Supervisor	5-Feb-23	E-23-1234		8:00 AM	4:00 PM	8.00	RT	1	$ 70.00	$ 560.00
20	8				E-23-1234				0.00
Labour Hours Entry

Cell Formulas
Range	Formula
I2:I7	I2	=SUMPRODUCT((--($H2=$C$13:$C$5000))*($I$13:$I$5000))
J2:J7	J2	=SUMPRODUCT((--($H2=$C$13:$C$5000))*($L$13:$L$5000))
K13:K19	K13	=VLOOKUP(J13,$O$3:$P$4,2,FALSE)
L13:L19	L13	=VLOOKUP(C13,$Q$3:$R$12,2,FALSE)
M13:M19	M13	=(I13*K13)*L13
E13:E20	E13	=$C$2
I13:I20	I13	=(H13-G13)*24

Cells with Data Validation
Cell	Allow	Criteria
J13:J20	List	=$O$3:$O$4
C13:C20	List	=$Q$3:$Q$12

Amazing- thank you!

 

[awoohaw]

My pleasure.

 

[JenMcG]

Amazing- thank you!

Actually, I may need to lock out the formulas. Is there a way to prevent the #N/A being return and instead have them be 0.00 in order to make your formulas run correctly?

 

[awoohaw]

Yes, I'll post updated formulas in a minute.