Question regarding equation output if cell value is negative
Hello all.
In a budget tracking sheet I created, I have a row titled "Remaining" that subtracts monthly input spending from the original "Bid" column. In the case that a value in the "Remaining" column is negative (over budget) I would like this number to be subtracted from a contingency row I have in my bid column where everything is totaled and output the result of that equation to my remaining column in in the contingency row. I have included a screenshot labeled to clarify.
Thanks in advance!
Best Answer
-
You should be able to do this with the SUMIFS() function based on the condition that <0.
=Bid@row + SUMIFS(Remaining2:Remaining22, Remaining2:Remaining22, <0)
Where the 2:22 rows are dependent on the rows you'd want to subtract from your contingency. Be cautious of this in case you have any parent formulas that would be negative so that you don't double count. If that's the case you would have to put an additional condition into your SUMIFS() to omit anything with COUNT(CHILDREN())>0.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
Answers
-
You should be able to do this with the SUMIFS() function based on the condition that <0.
=Bid@row + SUMIFS(Remaining2:Remaining22, Remaining2:Remaining22, <0)
Where the 2:22 rows are dependent on the rows you'd want to subtract from your contingency. Be cautious of this in case you have any parent formulas that would be negative so that you don't double count. If that's the case you would have to put an additional condition into your SUMIFS() to omit anything with COUNT(CHILDREN())>0.
Jason Tarpinian - Sevan Technology
Smartsheet Aligned Partner
-
Jason,
It worked perfectly! Thanks so much for your help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!