Sum the Absolute Values

Excel Formulas › Sum

All versionsSUMPRODUCT

Add up magnitudes, ignoring sign — total variance regardless of direction, total movement up or down. SUMPRODUCT with ABS sums the absolute values in one cell.

Quick formula: sum the absolute values of B2:B100:
=SUMPRODUCT(ABS(B2:B100))
ABS strips the sign from each value; SUMPRODUCT adds the magnitudes — no helper column needed.

Functions used (tap for the full reference guide):

SUMPRODUCTABS

The example

+5, −3, +2 sum to 10 in magnitude.

AB
1Value
2+5
3−3
4+2
5Sum of |values|10

The formula

Sum without signs:

=SUMPRODUCT(ABS(B2:B100)) // |+5|+|-3|+|+2| = 10

How it works

ABS over the array, then sum:

  1. ABS(B2:B100) returns an array of magnitudes (signs removed).
  2. SUMPRODUCT totals that array — the sum of absolute values.
  3. A plain =SUM(ABS(range)) needs Ctrl+Shift+Enter (array entry) in older Excel; SUMPRODUCT works without it.
  4. For a conditional absolute sum, multiply in a condition array, as with any SUMPRODUCT.

Total absolute variance across a budget is exactly this — it measures total deviation whether over or under. Compare to a plain SUM, which lets overs and unders cancel out.

Try it: interactive demo

Live demo

Values (signs allowed).

Sum |x| · Plain sum

Variations

Array SUM

Ctrl+Shift+Enter (older):

=SUM(ABS(range))

Conditional abs sum

Add a condition:

=SUMPRODUCT((region="East")*ABS(amt))

Largest absolute

Max magnitude:

=MAX(ABS(range))

Pitfalls & errors

SUM(ABS()) is an array formula. It needs Ctrl+Shift+Enter pre-365; SUMPRODUCT avoids that.

Text in range. ABS errors on text — ensure the range is numeric.

Don’t confuse with plain SUM. Absolute sum ignores cancellation; that’s the point, but make sure it’s what you want.

Practice workbook

📊
Download the free Sum the Absolute Values practice workbook
An absolute-sum sheet with the array, conditional, and max-magnitude variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sum absolute values in Excel?
Use =SUMPRODUCT(ABS(range)). ABS removes the signs and SUMPRODUCT adds the magnitudes, with no array entry needed.
Can I use SUM(ABS()) instead?
Yes, but it's an array formula needing Ctrl+Shift+Enter before Excel 365. SUMPRODUCT works as a normal formula.
How do I sum absolute values that meet a condition?
Multiply in a condition: =SUMPRODUCT((region="East")*ABS(amount)).

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: SUMPRODUCT formula · Nth root & powers · Budget vs actual variance

Function references: SUMPRODUCT · ABS