How to Sum Absolute Values Without Changing Negative Numbers in Excel

Excel can total a range as absolute values so minus signs are ignored, while other cells still keep normal plus and minus math.

What the question is asking

The request is specific: total the values in Column B, but treat every negative entry as a positive amount in that one total.

• Column B needs a sign-free total.
• Column C should still use the original signed numbers.
• The two calculations should stay independent.

Formula to total values without negative signs

Use the absolute value of each cell first, then add the results.

Replace range with the cells you want to total. For Column B, a real example is shown below.

Here, every negative number in B10:B25 is converted to a positive value before Excel adds the total.

Important clarification about Column C

The ABS total is separate

The absolute-value formula is only for the summary total. It does not rewrite the values stored in Column B.

Keep Column C on signed math

If Column C should remain as Column A plus or minus Column B, leave that formula unchanged and continue using the original signs there.

What is not supported by this formula

Using =SUM(ABS(range)) does not make Column C ignore negative signs. It only changes the one cell where you place the total formula.

Best when you need a summary amount without subtracting negatives.

Keep raw values for formulas

Best when downstream calculations still depend on plus and minus signs.

Check the total cell and a Column C row so you confirm each formula behaves differently.

Example of the distinction

The same negative value can behave differently depending on which formula uses it.

How the worksheet should behave

If a cell in Column B is negative, the summary total can still count it as positive by using ABS. That does not mean Column C should also ignore the sign.

• Summary total: ignores the minus sign.
• Column C formula: still uses the real signed value.
• Result: one worksheet can support both needs at the same time.

A quick verification check

After entering the total formula, compare the summary cell with a normal SUM result. The ABS version should be larger whenever the range contains negative numbers.

Then inspect a Column C row and confirm it still reflects the original plus or minus relationship from Columns A and B.

Use ABS for the total, not for every worksheet formula

The direct fix is =SUM(ABS(range)). It gives you a total that ignores minus signs, while Column C can stay on its original signed calculation from Column A and Column B.

Complete This Workflow with WPS Office

Use Word, Excel, and PPT for FREE

Free Download

WPS Office can calculate the magnitude of positive and negative values directly in WPS Spreadsheets.

1. Open a duplicate workbook in WPS Spreadsheets and select the cell that should show the magnitude-only total.
2. Enter =SUMPRODUCT(ABS(B2:B20)), replacing the example range with the cells that contain the positive and negative values.
3. Leave the existing signed calculation in the other column unchanged so it continues to show the net total.
4. Test with values such as 10, -4, and -6. The absolute total should be 20 while the signed total should be 0.

SUMPRODUCT avoids changing the stored numbers or requiring a helper column.

Sum Absolute Values Without Changing Negative Numbers in Excel FAQs

Will using ABS for the total change the numbers stored in Column B?

No. ABS only changes how the selected formula reads the values for that result cell. The original numbers in Column B remain unchanged, including their negative signs.

How can I verify that Column C is still calculating correctly?

Click a cell in Column C and inspect its formula bar. If it still uses the original signed references to Column A and Column B, the row calculation is unchanged even though your separate summary total ignores minus signs.

What if I want only the total to ignore signs, not each row result?

Put the ABS-based formula in a dedicated summary cell only. Leave each row formula, including Column C, on its normal signed calculation so only the final total ignores negative signs.

Why does Column C still show a negative effect from Column B?

Because Column C uses the original signed numbers from the worksheet. The ABS function only applies to the separate total formula, not to every other formula that references Column B.