# How to zero out cell if filtered sum is less than 0

I currently have the exact function set up here: Summing a column, filtered based on another column in Google Spreadsheet

I would like to know how to make the value 0 if the real sum is less than 0. Any help?

Additionally, if the sum from all the cells I'm adding returns no value at all (if no cells meet the criteria), I'd like to make the result "0".

• Please don't vandalize your question post. I understand you've got a partial answer so far, but you're asking two separate questions here. This is why we encourage askers to limit themselves to one question per question post. I suggest you edit this question to leave the one question that has been answered, and create a new question for the other, unanswered question.
– ale
Commented Jan 13, 2014 at 20:22
• Good practise is to accept the answer, so that future readers will notice. Read more tour that in our help center. Commented Jan 19, 2014 at 13:14

## 2 Answers

For your first question, I'd expect the `MAX()` function to give you what you need.

``````=MAX(SUM(A1:A5),0)
``````

MAX() returns the maximum value in a list of arguments. In this case, if the SUM() returns a value less than zero (i.e., negative) it'll return the zero instead.

• This works great. Thanks! I'm still getting "#N/A" in the cell because there's no cells in A1:A5 that meet the criteria. Can I force this to zero somehow? The "#N/A" breaks my entire spreadsheet. Commented Jan 12, 2014 at 5:00

I hope either of these would serve:

=iferror(if(sumif(B2:B6,"Yes",A2:A6)<0,0,SUMIF(B2:B6,"Yes",A2:A6)),0)

or

=if(sum(filter(A1:A6,B1:B6="Yes"))<0,0,sum(filter(A1:A6,B1:B6="Yes")))

For the avoidance of doubt, the data is assumed to be laid out so:

```+-------+-----------+
| value | condition |
+-------+-----------+
|    10 |  No       |
|   -10 | Yes       |
|    23 | Yes       |
|     4 |  No       |
|    34 | Yes       |
+-------+-----------+ ```