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

  • 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 Jan 13 '14 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 Tuinstra Jan 19 '14 at 13:14
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.

| improve this answer | |
  • 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. – Jeremy P. Beasley Jan 12 '14 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       |
+-------+-----------+ 
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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