How to Sum Only Negative Values in an Excel Table
Question details
The user needs to calculate the total sum of only the negative numbers within a specific column of a structured Excel table.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- When managing financial tables, expense reports, or data analyses containing mixed positive and negative values, the user needs to isolate and aggregate only the negative figures (like expenses or deductions).
- Observed behavior
- The user wants to apply a specific formula to filter out positive numbers and automatically calculate the sum of negative values in the "Amount" column.
Ensure your dataset is properly formatted as a structured table and identify the exact name of the table and the column containing the amounts you wish to sum.
Use the SUMIF Function
The SUMIF function allows you to conditionally sum values that are less than zero, ignoring all positive figures.
The SUMIF function is specifically designed to add up values in a range that meet a single condition. By using a less-than-zero criterion ("<0"), the function evaluates the targeted column and automatically excludes zero and positive numbers from the final calculation.
Click on an empty cell in your worksheet where you want the calculated total of the negative values to appear.
Type the formula =SUMIF(Table1[Amount], "<0", Table1[Amount]). Make sure to replace 'Table1' with the actual name of your table, and 'Amount' with the actual header of your target column.
Press the Enter key. The cell will now display the total sum of all the negative numbers located in the specified column.
Easily Calculate Conditional Sums with WPS Spreadsheet
WPS Spreadsheet offers powerful built-in formula support, including the SUMIF function, allowing you to seamlessly calculate negative values, manage financial tables, and analyze data efficiently for free.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data table.
- 2. Select a cell: Click on the specific cell where you want the sum of the negative numbers to be displayed.
- 3. Apply the SUMIF formula: Type =SUMIF(, highlight the column containing your data, enter ","<0") to set the condition, and press Enter to instantly see your result.
Frequently Asked Questions
How do I sum positive values instead of negative ones?
You can simply change the criterion in your SUMIF formula from less-than to greater-than. Use =SUMIF(Table1[Amount], ">0", Table1[Amount]) to calculate the total of all positive numbers.
Why is my SUMIF formula returning zero or an error?
This typically occurs if the table name or column header in your formula doesn't perfectly match the actual names in your worksheet. It can also happen if your numbers are accidentally formatted as text. Double-check your spelling and ensure the column is formatted as Numbers or Currency.
Can I sum negative values based on an additional text condition?
Yes, but you will need to use the SUMIFS function instead, which supports multiple criteria. For example, =SUMIFS(Table1[Amount], Table1[Amount], "<0", Table1[Description], "Refund") will sum negative amounts only if the adjacent description is 'Refund'.
