Total dollar amount if hours are over 40.

[mcjohnson3211]

Thanks in advance. I'm running into another formula I can't figure out through my research.

I'm doing timekeeping using Excel. And I need only to total the amounts that are over 40 hours. But the rates of each role are different, so once the total of a team goes over 40 hours, I need to total the cost of hours for everything over 40. I hope that makes sense.

Team Role	Date	Hours	Hour Fee
Solution Manager	9/1/2023​	0.5​	$ 100.00			Role Hourly Rate	Hourly Rate	Monthly Total Hours
Solution Architect	9/1/2023​	1​	$ 200.00			Account Manager	$ 150	0​
Delivery Manager	9/1/2023​	0.25​	$ 37.50			Account Director	$ 185	0​
Solution Architect	9/4/2023​	0.5​	$ 100.00			Solution Architect	$ 200	5​
Solution Architect	9/4/2023​	1.5​	$ 300.00			Solution Manager	$ 200	2​
Solution Architect	9/4/2023​	0.5​	$ 100.00			Database Developer	$ 225	0​
Delivery Manager	9/5/2023​	0.5​	$ 75.00			Software Engineer	$ 225	0​
Delivery Manager	9/5/2023​	1.5​	$ 225.00			Campaign Developer	$ 125	0.08​
Solution Manager	9/6/2023​	1​	$ 200.00			Deliverability Specialist	$ 175	0​
Delivery Manager	9/6/2023​	1.25​	$ 187.50			Analytics Manager	$ 175	0​
Delivery Manager	9/6/2023​	0.5​	$ 75.00			Strategy Manager	$ 175	0​
Data Services	9/6/2023​	0.58​	$ 101.50			Delivery Manager	$ 150	9.25​
Campaign Developer	9/6/2023​	0.08​	$ 10.00			Data Services	$ 175	1.33​
Solution Manager	9/7/2023​	0.5​	$ 100.00
Solution Architect	9/7/2023​	1​	$ 200.00			Total for Highlighted in blue		16.25​
Solution Architect	9/7/2023​	0.5​	$ 100.00			Total if over 40 hours		-23.75​
Delivery Manager	9/7/2023​	1.5​	$ 225.00			Cost if over 40 hours
Delivery Manager	9/7/2023​	0.5​	$ 75.00
Data Services	9/7/2023​	0.17​	$ 29.75
Delivery Manager	9/8/2023​	0.75​	$ 112.50
Delivery Manager	9/8/2023​	1.5​	$ 225.00
Data Services	9/8/2023​	0.58​	$ 101.50
Delivery Manager	9/13/2023​	0.5​	$ 75.00
Delivery Manager	9/13/2023​	0.5​	$ 75.00

 

[awoohaw]

How do you determine which rate to apply for the overage. (i am assuming all roles are in one team).
If not I do not understand your data. Can you please give a hand calculated expected result of when a team or role goes over 40 hours?

 

[mcjohnson3211]

So, the people highlighted in blue are one team we only charge for going over 40 hours. Everyone else I will be charging no matter the amount of hours. So, if I need to, I can break those up into two groups. i.e., Team 1 (highlighted in blue), Team 2 (everyone else).

 

[mcjohnson3211]

So, the people highlighted in blue are one team we only charge for going over 40 hours. Everyone else I will be charging no matter the amount of hours. So, if I need to, I can break those up into two groups. i.e., Team 1 (highlighted in blue), Team 2 (everyone else).

Sorry... So, the people highlighted in blue are the only people we need to charge if the hours go over 40 hours.

 

[awoohaw]

Okay. So, the rate makes no difference. Do you just want a number of hours? So we can ignore anything to do with hourly rates?

try this then:

Book1
	A	B	C	D	E	F	G	H	I
1	Team Role		Date	Hours
2	Solution Manager	Hourly Capped Team	9/1/2023	0.5			Role Hourly Rate		Monthly Total Hours
3	Solution Architect	Hourly Capped Team	9/1/2023	1.0			Account Manager	Hourly Capped Team	0
4	Delivery Manager	Hourly Capped Team	9/1/2023	0.3			Account Director	Hourly Capped Team	0
5	Solution Architect	Hourly Capped Team	9/4/2023	0.5			Solution Architect	Hourly Capped Team	38.5
6	Solution Architect	Hourly Capped Team	9/4/2023	35.0			Solution Manager	Hourly Capped Team	2
7	Solution Architect	Hourly Capped Team	9/4/2023	0.5			Database Developer		0
8	Delivery Manager	Hourly Capped Team	9/5/2023	0.5			Software Engineer		0
9	Delivery Manager	Hourly Capped Team	9/5/2023	1.5			Campaign Developer		0.08
10	Solution Manager	Hourly Capped Team	9/6/2023	1.0			Deliverability Specialist		0
11	Delivery Manager	Hourly Capped Team	9/6/2023	45.0			Analytics Manager		0
12	Delivery Manager	Hourly Capped Team	9/6/2023	0.5			Strategy Manager		0
13	Data Services		9/6/2023	100.0			Delivery Manager	Hourly Capped Team	53
14	Campaign Developer		9/6/2023	0.1			Data Services		100.75
15	Solution Manager	Hourly Capped Team	9/7/2023	0.5
16	Solution Architect	Hourly Capped Team	9/7/2023	1.0			Hourly Capped Team Total	Hourly Capped Team	93.5
17	Solution Architect	Hourly Capped Team	9/7/2023	0.5			Total over 40 hours		53.5
18	Delivery Manager	Hourly Capped Team	9/7/2023	1.5
19	Delivery Manager	Hourly Capped Team	9/7/2023	0.5
20	Data Services		9/7/2023	0.2
21	Delivery Manager	Hourly Capped Team	9/8/2023	0.8
22	Delivery Manager	Hourly Capped Team	9/8/2023	1.5
23	Data Services		9/8/2023	0.6
24	Delivery Manager	Hourly Capped Team	9/13/2023	0.5
25	Delivery Manager	Hourly Capped Team	9/13/2023	0.5
26
mcjohnson3211

Cell Formulas
Range	Formula
I3:I14	I3	=SUMIF($A$2:$A$25,G3,$D$2:$D$25)
I16	I16	=SUMIF($B$2:$B$25,H16,$D$2:$D$25)
I17	I17	=IF(I16>40,I16-40,"")

 

[mcjohnson3211]

Okay. So, the rate makes no difference. Do you just want a number of hours? So we can ignore anything to do with hourly rates?

Well, the rates do matter. I need to total the cost of Team 1 once 40 hours have been reached. I will need to total Team 2 for anything above 0.

 

[awoohaw]

with what you've given you cannot total team 1 payment since you have no rules on how to allocate the wage to the overage.

as I asked earlier... please give a hand calculation of what you expect for the payment for the hours over 40 of team 1.

 

[mcjohnson3211]

Sorry, I tried downloading the minisheet addon but it didn't work. I think my company has protection against it. But, I'm using a VLOOKUP to get the cost of the hours in column E.

=VLOOKUP(B2,$H$3:$I$14,2,0)*D2

 

[mcjohnson3211]

with what you've given you cannot total team 1 payment since you have no rules on how to allocate the wage to the overage.

as I asked earlier... please give a hand calculation of what you expect for the payment for the hours over 40 of team 1.

So I have updated the data to add more numbers to get us beyond the 40 hours.
The total now is 48 hours (8 over).

2 hours over of Solution Architect = $400
1 hours over of Deliver Manager = $150
5 hours over of Account Manager = $750
Totaling Cost = $1,300

Team Role	Date	Hours	Hour Fee
Delivery Manager	9/1/2023​	0.25​	$ 37.50			Role Hourly Rate	Hourly Rate	Monthly Total Hours
Solution Architect	9/1/2023​	1​	$ 200.00			Team 1
Solution Architect	9/4/2023​	1.5​	$ 300.00			Account Manager	$ 150	8​
Solution Architect	9/4/2023​	0.5​	$ 100.00			Account Director	$ 185	0​
Account Manager	9/5/2023​	0.5​	$ 75.00			Solution Architect	$ 200	23​
Account Manager	9/5/2023​	0.5​	$ 75.00			Solution Manager	$ 200	1​
Delivery Manager	9/5/2023​	1.5​	$ 225.00			Delivery Manager	$ 150	16​
Delivery Manager	9/5/2023​	0.5​	$ 75.00			Team 2		0​
Account Manager	9/6/2023​	0.5​	$ 75.00			Database Developer	$ 225	0​
Account Manager	9/6/2023​	0.5​	$ 75.00			Software Engineer	$ 225	0​
Solution Manager	9/6/2023​	1​	$ 200.00			Campaign Developer	$ 125	0​
Delivery Manager	9/6/2023​	1.25​	$ 187.50			Deliverability Specialist	$ 175	0​
Delivery Manager	9/6/2023​	0.5​	$ 75.00			Analytics Manager	$ 175	0​
Delivery Manager	9/7/2023​	1.5​	$ 225.00			Strategy Manager	$ 175	0​
Delivery Manager	9/7/2023​	0.5​	$ 75.00			Data Services	$ 175	0​
Solution Architect	9/7/2023​	1​	$ 200.00
Delivery Manager	9/8/2023​	1.5​	$ 225.00			Total for Highlighted in blue		48​
Delivery Manager	9/11/2023​	1.5​	$ 225.00			Total if over 40 hours		8​
Delivery Manager	9/12/2023​	0.75​	$ 112.50			Cost if over 40 hours
Delivery Manager	9/13/2023​	0.5​	$ 75.00
Account Manager	9/13/2023​	0.5​	$ 75.00
Delivery Manager	9/13/2023​	0.75​	$ 112.50
Delivery Manager	9/13/2023​	0.5​	$ 75.00
Delivery Manager	9/13/2023​	1​	$ 150.00
Account Manager	9/14/2023​	0.5​	$ 75.00
Delivery Manager	9/14/2023​	2​	$ 300.00
Delivery Manager	9/15/2023​	0.5​	$ 75.00
Solution Architect	9/15/2023​	19​	$ 3,800.00
Delivery Manager	9/15/2023​	1​	$ 150.00
Account Manager	9/15/2023​	5​	$ 750.00

 

[awoohaw]

So I have updated the data to add more numbers to get us beyond the 40 hours.
The total now is 48 hours (8 over).

2 hours over of Solution Architect = $400
1 hours over of Deliver Manager = $150
5 hours over of Account Manager = $750
Totaling Cost = $1,300

Team Role	Date	Hours	Hour Fee
Delivery Manager	9/1/2023​	0.25​	$ 37.50			Role Hourly Rate	Hourly Rate	Monthly Total Hours
Solution Architect	9/1/2023​	1​	$ 200.00			Team 1
Solution Architect	9/4/2023​	1.5​	$ 300.00			Account Manager	$ 150	8​
Solution Architect	9/4/2023​	0.5​	$ 100.00			Account Director	$ 185	0​
Account Manager	9/5/2023​	0.5​	$ 75.00			Solution Architect	$ 200	23​
Account Manager	9/5/2023​	0.5​	$ 75.00			Solution Manager	$ 200	1​
Delivery Manager	9/5/2023​	1.5​	$ 225.00			Delivery Manager	$ 150	16​
Delivery Manager	9/5/2023​	0.5​	$ 75.00			Team 2		0​
Account Manager	9/6/2023​	0.5​	$ 75.00			Database Developer	$ 225	0​
Account Manager	9/6/2023​	0.5​	$ 75.00			Software Engineer	$ 225	0​
Solution Manager	9/6/2023​	1​	$ 200.00			Campaign Developer	$ 125	0​
Delivery Manager	9/6/2023​	1.25​	$ 187.50			Deliverability Specialist	$ 175	0​
Delivery Manager	9/6/2023​	0.5​	$ 75.00			Analytics Manager	$ 175	0​
Delivery Manager	9/7/2023​	1.5​	$ 225.00			Strategy Manager	$ 175	0​
Delivery Manager	9/7/2023​	0.5​	$ 75.00			Data Services	$ 175	0​
Solution Architect	9/7/2023​	1​	$ 200.00
Delivery Manager	9/8/2023​	1.5​	$ 225.00			Total for Highlighted in blue		48​
Delivery Manager	9/11/2023​	1.5​	$ 225.00			Total if over 40 hours		8​
Delivery Manager	9/12/2023​	0.75​	$ 112.50			Cost if over 40 hours
Delivery Manager	9/13/2023​	0.5​	$ 75.00
Account Manager	9/13/2023​	0.5​	$ 75.00
Delivery Manager	9/13/2023​	0.75​	$ 112.50
Delivery Manager	9/13/2023​	0.5​	$ 75.00
Delivery Manager	9/13/2023​	1​	$ 150.00
Account Manager	9/14/2023​	0.5​	$ 75.00
Delivery Manager	9/14/2023​	2​	$ 300.00
Delivery Manager	9/15/2023​	0.5​	$ 75.00
Solution Architect	9/15/2023​	19​	$ 3,800.00
Delivery Manager	9/15/2023​	1​	$ 150.00
Account Manager	9/15/2023​	5​	$ 750.00

So, who's 8 hours is the overtime? Or do you want a weighted average?