Function Problems

Excel: Add Negative Numbers as Positive Values in a Total

Phi Hung VoPhi Hung Vo Nov 12, 2026 869 views

Question details

Create a total that adds negative and positive numbers as positive amounts.

Excel: Add Negative Numbers as Positive Values in a Total
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to calculate the total of a range without allowing negative values to reduce the result.
Observed behavior
Each number in the selected range should be treated by its absolute value before the total is calculated.
Before you start

Confirm that the cells contain real numeric values rather than numbers stored as text. Replace E3:E7 in the formula with the range you want to total.

Solution 1Recommended

Use SUM with ABS to total every value as a positive amount

Use this method in current Excel versions when you want to add the absolute value of every number in a range.

The ABS function removes the negative sign from a number without changing its magnitude. Wrapping ABS inside SUM produces a total where both -10 and 10 contribute 10.

1
Select the total cell

Click the blank cell where you want the positive-value total to appear.

2
Enter the absolute-value total formula

Type =SUM(ABS(E3:E7)), replacing E3:E7 with your own cell range.

3
Calculate the result

Press Enter to total all values as positive amounts.

Use SUM with ABS to total every value as a positive amount
Formula result: For values of 10, -5, and -8, the formula returns 23 rather than -3.
WPS Spreadsheets

Total Negative Values as Positive Amounts in WPS Spreadsheets

WPS Spreadsheets supports the ABS function, so you can convert negative values to positive amounts within a formula and calculate an accurate total without changing the source values.

  1. 1. Open the worksheet in WPS Spreadsheets: Open your Excel workbook in WPS Office and select the worksheet containing the values.
  2. 2. Select the result cell: Click the empty cell where you want the total to appear.
  3. 3. Enter the ABS total formula: Type =SUM(ABS(E3:E7)) and replace E3:E7 with your required range.
  4. 4. Confirm the formula: Press Enter to display the total with negative numbers counted as positive amounts.
Use ABS to include negative amounts as positive values in a spreadsheet total.Calculate range totals with familiar Excel-style formulas.Open, edit, and save Microsoft Excel .xlsx and .xls files with format compatibility.Use a lightweight spreadsheet interface with familiar formula entry and function tools.
microsoft office alternative - wps office

Frequently Asked Questions

What does the ABS function do in Excel?

ABS returns a number's absolute value. For example, ABS(-25) returns 25, while ABS(25) also returns 25.

How do I add positive and negative numbers without subtracting the negative values?

Enter =SUM(ABS(E3:E7)) in the result cell, replacing E3:E7 with your range. This adds every value by its magnitude.

Why does my ABS total not calculate in an older Excel version?

Older Excel versions may require an array formula for =SUM(ABS(E3:E7)). Use =SUMPRODUCT(ABS(E3:E7)) and press Enter as a compatible alternative.

Does the ABS formula change the negative numbers in my cells?

No. ABS changes values only within the calculation result. The original cells remain negative or positive as entered.