How to Return Zero When the Sum of Two Cells Is Negative in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that calculates the sum of two cells and returns zero if the result is negative.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Calculating a total from two cells while preventing negative totals from appearing in a worksheet or report.
- Observed behavior
- A standard SUM formula returns the negative sum instead of zero when the calculated total is less than zero.
Open your workbook in WPS Spreadsheets and identify the two cells you want to sum. Make sure both cells contain numeric values so the MAX and SUM functions can calculate the result correctly.
Use =MAX(0,SUM(O18:P18)) to Return Zero for Negative Totals
Use this formula when the result cell should display the sum of two cells but never show a negative total.
The MAX function compares the SUM result with 0 and returns the larger value. If the sum is negative, 0 is larger, so the formula returns zero. Because the formula outputs a number, you can still use it in other calculations and apply number formatting.
Click the cell where you want the total to appear, then click inside the formula bar.
Type =MAX(0,SUM(O18:P18)), replacing O18:P18 with the two cells you want to sum.
Press Enter to apply the formula. WPS Spreadsheets now returns the sum when it is positive and zero when the sum is negative.
Press Ctrl+1, choose Number, set Decimal places to 3, and click OK. This keeps the result numeric rather than converting it to text.
Use WPS Spreadsheets to Cap Negative Sums at Zero
You can complete this calculation directly in WPS Spreadsheets with the formula =MAX(0,SUM(O18:P18)). It returns the positive sum as usual and automatically replaces any negative total with zero, without changing your existing workbook structure.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, open your workbook, and click the cell where you want the total to appear.
- 2. Enter the formula: Click the formula bar and type =MAX(0,SUM(O18:P18)), replacing O18:P18 with the two cells you need to sum.
- 3. Press Enter: Press Enter to calculate the formula. If the sum is negative, the cell displays 0 instead of a negative value.
- 4. Apply number formatting if required: Press Ctrl+1, choose Number, set Decimal places to 3, and click OK to keep the result numeric.
Frequently Asked Questions
Why does =MAX(0,SUM(O18:P18)) return zero instead of a negative number?
The MAX function compares the result of SUM(O18:P18) with 0 and returns the larger value. When the sum is negative, 0 is larger, so the formula displays 0.
Can I use this formula for more than two cells?
Yes. Replace the range O18:P18 with a larger range such as O18:O30 or a comma-separated list, and the formula will sum all included numeric cells before applying the MAX check.
How do I show three decimal places without converting the result to text?
Select the formula cell, press Ctrl+1, choose Number, set Decimal places to 3, and click OK. Number formatting changes only the display and keeps the value numeric.
Does this formula work when I open an Excel file in WPS Spreadsheets?
Yes. WPS Spreadsheets supports standard Excel formulas and opens .xlsx and .xls files, so you can use =MAX(0,SUM(range)) in existing workbooks.
What happens if the cells contain text instead of numbers?
The SUM function ignores text in the referenced range. If there are no numeric values, the sum is 0, and the formula returns 0.
