Hi Y'all, I hope you had a good weekend.
I'm trying to calculate the total cost of each individual per week. I feel a SumProduct should do the trick, but I've not managed to get the correct formula.
Could you please take a look at the attachment and assist me?
Thank you so much for your help!
Your existing calculations are not all correct. In E15 you are paying ST for all 41 hours then paying OT on 1 hour. ST should just be 40 hours.
I have put in a formula that calculates ST and OT for the week based on the date, and filled right. SUMPRODUCT does not come into play.
=MIN(SUMIFS(4:4,$3:$3,">"&(E$11-7),$3:$3,"<="&E$11),40)*$C12+MAX(0,SUMIFS(4:4,$3:$3,">"&(E$11-7),$3:$3,"<="&E$11)-40)*$D12
This first multiplies the ST rate times hours up to 40. Then it adds hours over 40, if any, times the OT rate.
Edit: The SUMIFS will sum the hours for all dates from after end-of-week date minus 7 (which is the first day of the week) up to the end-of-week date.
I sincerely appreciate this. I've been working on it for days now (one way/formula or another) and you've put it together. Thank you.
You're welcome! Please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."
If a member helped you solve your problem, consider adding to their reputation by clicking on the star icon below their name.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks