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,

 

[awoohaw]

The last column did not need to be updated because it was dependent on one of these two. And, since that is fixed, it is not a concern. Update these in Row 13, then copy down.

Mr Excel Questions2.xlsm
	K	L
13	1	$30.00
Labour Hours Entry

Cell Formulas
Range	Formula
K13	K13	=IF(J13="",0, VLOOKUP(J13,$O$3:$P$4,2,FALSE) )
L13	L13	=IF(C13="",0, VLOOKUP(C13,$Q$3:$R$12,2,FALSE) )

 

[awoohaw]

If you think one of the posts has given you an answer, please mark it as the answer.

Best wishes!

 

[JenMcG]

The last column did not need to be updated because it was dependent on one of these two. And, since that is fixed, it is not a concern. Update these in Row 13, then copy down.

Mr Excel Questions2.xlsm
	K	L
13	1	$30.00
Labour Hours Entry

Cell Formulas
Range	Formula
K13	K13	=IF(J13="",0, VLOOKUP(J13,$O$3:$P$4,2,FALSE) )
L13	L13	=IF(C13="",0, VLOOKUP(C13,$Q$3:$R$12,2,FALSE) )

Really appreciate your help today!

 

[awoohaw]

My pleasure. Please remember to mark the post that answered your question as the answer.

Best wishes!

 

[alansidman]

An alternative is with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Role"}, {{"Hours", each List.Sum([Total Hours]), type number}, {"SubRate", each List.Sum([Rate]), type number}})
in
    #"Grouped Rows"