How to Sum Absolute Value in Excel: 6 Easy Ways

In this article, we will discuss 6 simple ways to sum absolute value in Excel. We will consider the below dataset that contains both negative and positive numbers.

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

Steps:

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

Write 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 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:

Write the below 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 Value

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

Write the following formula to 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 Value with a Function in Excel VBA

Steps:

Go to the bottom section of the worksheet.
Press the right button of the mouse.
Choose the View Code option from the Context Menu.

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

Write the following VBA code on the VBA module, and save this code.

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