# How to sum absolute values in Excel

Wrap the range in ABS inside SUMPRODUCT: `=SUMPRODUCT(ABS(B2:B8))` adds every value as a positive number.

**Excel:** `=SUMPRODUCT(ABS(B2:B8))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Item | Count variance |  | Total variance |
| Bolts | -14 |  |  |
| Nuts | 22 |  |  |
| Washers | -9 |  |  |
| Screws | 31 |  |  |
| Rivets | -6 |  |  |
| Anchors | 0 |  |  |
| Hinges | -18 |  |  |

Formula in E1: `=SUMPRODUCT(ABS(B2:B8))`

Result: 100

## 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.

## Other ways

- `=SUMIF(B2:B8,">0")-SUMIF(B2:B8,"<0")`: Positives minus negatives gives the same total without an array, in every version.
- `=SUM(B2:B8)`: 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.

## 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))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-absolute-values