-1

I am trying to calculate the total number of overtime hours worked by staff each week. I have a formula that seems to do the job OK, but I feel there could be a better or more concise way to write it.

Basically I have a list of all the staff, and the number of hours they have worked that week. My SUMIF() finds the hours over 40, adds them together, and then subtracts 40 for each of the staff with overtime.

=SUMIF(B2:B13,">40")-(40*COUNTIF(B2:B13,">40"))

Is there a way to subtract values based on a SumIf range, rather than having to include the 40*COUNTIF()... ? Something like =SUMIF(B2:B13,">40",[B2:B13]-40) (I know this doesn't work, it's just how I pictured it when I tried it out).

0

You came pretty close with SUMIF. I've used it in my answer too; though as a proof - not as the main calculation of the actual overtime.

Here's my snapshot of a dummy payroll and analysis. enter image description here

This is the basic format that I would use every week.
The first six cells (Column B) establish the number of staff, Base Hours, Total Hours and OT Hours. The sixth cell is a proof of the OT hours. It should always be zero.

=(sumif(B10:B17,">40"))-((COUNTIF(B10:B17,">40"))*B2)-B5

To summarise: sum the OT hours then deduct the base hours for those staff (the number of instances >40 hours x the 40 base hours) and deduct the value in cell B5. The answer should be zero. If not, there's a problem in the data.

I've included a "Detailed Proof" only to show where the OT hours arise and the totals. This is just for the sake of this explanation. I wouldn't do this every week.

0

Another approach, perhaps a bit easier on the brain:

=ArrayFormula(SUM(IF(B2:B13>40,B2:B13-40)))

In English: "Cycle through (i.e., array) all values from B2:B13, only looking at those values over 40. For each match found, subtract 40 then sum all of the collected differences."

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.