# Excluding negative values from summation

Is there a way to get a function to return any result below `0` as `0` or not include negative numbers in sums?

I'm using it to track my total overtime through the year at work. So right now I have it so if I work over 70hrs a week, say 85 it will return 15 hours. That works fine, but when I try to sum them all up I get -70 hours for each week I haven't worked yet.

Use the following formula to accomplish that:

``````=SUMIF(range;">"&0)
``````

The `range` is of course the one you set it to be !!

EDIT 29-03-2013; 23:00 CEST

IMPORTANT: This is only valid if you work at least 70 hours per week, each week. It is just as important to know if you have worked less !! Therefore you need to change the way you calculate the "overtime". The following formula will show only a calculated value, if a "hours worked" value has been entered:

`=IF(B2="";"";B2-70)`

See example file, column E.

EDIT 29-03-2013; 22:30 CEST
I've created an example file: Excluding Negative Values. Here I tried to clarify the problem, the way the OP sees it. In doing so, I incorporated the other answers as well.

The solution given by SpreadsheetPro.net works perfectly. The statement about the `SUMIF(S)` not being available in Google Spreadsheet, is false.

The solution handed by Al, covers a different approach. Instead of calculating `Hours Worked MINUS Hours Working Week`, Al is telling the formula to return `ZERO` is value is below `ZERO`, see column D in example.

You can also use `SUM(FILTER(range;criterion))`

``````SUM(FILTER(A:A;A:A>0))
``````

This makes it more flexible if you want to have more than one criterion (as there is no SUMIFS function in Google Spreadsheet). In that case you can stack multiple criteria in the FILTER function.

• This was -1 when I came here but it works. >:-. What the hell. Here's my +1 for @Javaaaa! – SteakOverflow Feb 28 '20 at 16:26

There is a `MAX()` function.

``````=MAX(0,SUM(A1:A7)-70)
``````

It returns the highest value in a range of numbers. In this case, `0`, or whatever the value of `SUB(A1:A7) - 70` is. If the latter is negative, this returns a 0.