How to sum absolute values in Excel
Formula (Excel and Google Sheets)
=SUMPRODUCT(ABS(B2:B8))Wrap the range in ABS inside SUMPRODUCT: =SUMPRODUCT(ABS(B2:B8)) adds every value as a positive number.
- Result on the example
- 100 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
ABS removes the minus sign from each value, and SUMPRODUCT adds the results without needing array entry. Shortages and overages both count toward the total, which is what you want when measuring how far off a count is, rather than the net difference.
=SUMPRODUCT(ABS(B2:B8))Result100
B2:B87 rows × 1 col: -14, 22, -9, 31, …A range: 7 cells in one column.
ABS(B2:B8){7 rows × 1 col} 14, 22, 9, 31, …ABS: Absolute value.- number
B2:B87 rows × 1 col: -14, 22, -9, 31, …
- number
SUMPRODUCT(ABS(B2:B8))100SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
ABS(B2:B8){7 rows × 1 col} 14, 22, 9, 31, …
- array1
In Google Sheets
The same formula works in Google Sheets: =SUMPRODUCT(ABS(B2:B8)). We ran it in Google Sheets mode on the same data and it returns 100.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMIF(B2:B8,">0")-SUMIF(B2:B8,"<0") | 100 | Positives minus negatives gives the same total without an array, in every version. |
| =SUM(B2:B8) | 6Different result | The net variance, where overages and shortages cancel. Net variance: 6. |
Common mistakes
=SUM(ABS(B2:B8))works in Excel 365 but needs Ctrl+Shift+Enter in older versions and ARRAYFORMULA in Google Sheets. SUMPRODUCT works everywhere.=ABS(SUM(B2:B8))takes the absolute value of the net total (6), not the total of absolute values.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum absolute values
E1, add up the size of every variance in B2:B8, treating shortages and overages alike.Questions
How do I get the absolute value of one cell?
Use =ABS(B2).
How do I sum absolute differences between two columns?
Use =SUMPRODUCT(ABS(B2:B8-C2:C8)).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.