How to Sum Absolute Value in Excel: 6 Easy Ways

We will consider a simple dataset that contains both negative and positive numbers.

Method 1 – Use the ABS Function Inside the SUM Function to Sum Absolute Values

Steps:

Add a new row in the dataset to calculate the sum.

Use the following formula on cell B12.

=SUM(ABS(B5:B9))

Press Enter to get the result.

The ABS function returns the absolute value of the selected range. The SUM function adds all the absolute values.

Method 2 – Get the Absolute Value of the Sum Result Using SUM Inside ABS Function

Steps:

Go to cell B12 and put the following formula there.

=ABS(SUM(B5:B9))

Press Enter to get the result.

Method 3 – Combination of Two SUMIF Functions to Sum Absolute Values

Steps:

Use this formula at cell B12.

=SUMIF(B5:B9,">0")-SUMIF(B5:B9,"<0")

Press Enter.

Method 4 – Combination of SUM and SUMIF Functions to Sum Absolute Values

Steps:

Write the following formula on cell B12.

=SUM(SUMIF(B5:B9,{">0","<0"})*{1,-1})

Press Enter.

Method 5 – Get the Absolute Value of a Sum for Specific Values Using SUMIF and ABS Functions

Steps:

We have 2 lists of numbers on the dataset.

We will apply a condition within the SUMIF function: the sum of the positive numbers from list 1. From list 2, we will sum the negative numbers. We’ll add both results and apply ABS operation on the previous result.

Use the following formula in cell C11.

=ABS(SUMIF(B5:B9,">0")+SUMIF(C5:C9,"<0"))

Here, >0 is applied for positive numbers and <0 for negative numbers.

Press Enter.

Method 6 – Sum Absolute Values with a Function in Excel VBA

Steps:

Right-click on the sheet name at the bottom.
Choose the View Code option from the Context Menu.

The VBA window appears.
Choose the Module option from the Insert tab.

Use the following VBA code on the VBA module and save it.

Function ABS_SUM(range_1 As Range) As Double
Dim sum_1 As Double
Dim cell_1 As Range
Result = 0
On Error GoTo Done
For Each cell_1 In range_1
sum_1 = sum_1 + Abs(cell_1)
Next cell_1
Done:
ABS_SUM = sum_1
End Function