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.

3 Answers 3


Use the following formula to accomplish that:


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:


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

In your case something like:


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! Feb 28, 2020 at 16:26

There is a MAX() function.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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