Formula for Sum or just return a cell value
I am writing a 'difference' formula…the sum of cell 1 minus cell 2 is the difference. Easy. HOWEVER, if the value for cell 1 = 0 I want to return just the value of cell 2 and not subtract and get a negative number. Any ideas on combining these two? I know I'm close but I think I'm not connecting my formulas for IF and SUM.
Best Answer
-
Try this:
=IF(Cell1 = 0, Cell2, Cell1 - Cell2)
- IF(Cell1 = 0, Cell2,: This part of the formula checks if the value in Cell1 is 0. If it is, the formula returns the value in Cell2.
- Cell1 - Cell2): If Cell1 is not 0, the formula subtracts Cell2 from Cell1 and returns the result.
OR, if you are trying to avoid a negative number.
=IF(Cell1 < Cell2, Cell2, Cell1 - Cell2)
- IF(Cell1 < 0, Cell2,: This part of the formula checks if the value in Cell1 is less than Cell2. If it is, the formula returns the value in Cell2.
- Cell1 - Cell2): If Cell1 is not less than Cell2, the formula subtracts Cell2 from Cell1 and returns the result.
Answers
-
Is it just cell1 value minus cell2 value? If so I don't think you really need the SUM function.
Something like this should work:
=IF([cell 1]@row = 0, [cell 2]@row, [cell 1]@row - [cell 2]@row)
-
Try this:
=IF(Cell1 = 0, Cell2, Cell1 - Cell2)
- IF(Cell1 = 0, Cell2,: This part of the formula checks if the value in Cell1 is 0. If it is, the formula returns the value in Cell2.
- Cell1 - Cell2): If Cell1 is not 0, the formula subtracts Cell2 from Cell1 and returns the result.
OR, if you are trying to avoid a negative number.
=IF(Cell1 < Cell2, Cell2, Cell1 - Cell2)
- IF(Cell1 < 0, Cell2,: This part of the formula checks if the value in Cell1 is less than Cell2. If it is, the formula returns the value in Cell2.
- Cell1 - Cell2): If Cell1 is not less than Cell2, the formula subtracts Cell2 from Cell1 and returns the result.
-
@SteveAtPathtoSummit that worked like a charm (the second one) and I avoided a negative number. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!