Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

In your case something like:

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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