2

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

2
  • 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.
    – Jacob Jan
    Commented Jan 19, 2014 at 13:14

2 Answers 2

1

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.

1
  • 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
0

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

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.