# Using SUMIF to add amounts over a given number

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).

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. 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.

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."