When you sum the absolute values of a dataset in Excel that contains both positive and negative numbers, all the negative values are calculated as positives. For example 40 + (-10) returns 50 instead of 30.

You can use formulas to add absolute values in Excel. In this tutorial, we will use the following dataset to show how this can be done:

We will explain and demonstrate how the following 6 methods can be used to sum absolute values in Excel:

- Use two
**SUMIF****functions** - Employ the combination of the
**SUM function**and**SUMIF function** - Apply the combination of
**SUMPRODUCT function**and**ABS function** - Use the combination of
**SUM function**and**ABS function** - Convert the negative values in place and then use the
**SUM function** - Apply
**Excel VBA Code**

Contents

- 1 Method 1 – Use two SUMIF functions
- 2 Method 2 – Employ the combination of the SUM function and SUMIF function
- 3 Method 3 – Apply the combination of SUMPRODUCT function and ABS function
- 4 Method 4 – Use a combination of the SUM function and ABS function
- 5 Method 5 – Convert the negative values in place and then apply the SUM function
- 6 Method 6 – Apply Excel VBA Code

## Method 1 – Use two SUMIF functions

The **SUMIF function **sums up the cells specified by a given condition or criteria.

In this method, we use two **SUMIF functions** to sum the cells specified by a certain condition or criteria, by doing the following steps:

**Step 1** – Select **Cell B10** and enter the formula =SUMIF(B2:B9,”>0″)-SUMIF(B2:B9,”<0″) as follows:

**Step 2** – Press the Enter key and the sum of the absolute values will be displayed:

### Explanation of the formula

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

**SUMIF(B2:B9,”>0″)**adds all the positive numbers and returns the value 75.**SUMIF(B2:B9,”<0″)**adds all the negative numbers and returns the value -191.- The negative value is then subtracted from the positive value: 75-(-191). This results into 75 + 191 = 266.

## Method 2 – Employ the combination of the SUM function and SUMIF function

In this method, we use the combination of the **SUM function** and **SUMIF function** to get the sum of absolute values by doing the following:

**Step 1** – Select **Cell B10** and enter the formula **=SUM(SUMIF(B2:B9,{“>0″,”<0”})*{1,-1})** as follows:

**Step 2** – Press the Enter key and the result will be displayed:

### Explanation of the formula

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

**SUMIF(B2:B9,{“>0″,”<0”})**add the positive values and the negative values and returns the array**{75,-191}**.**SUMIF(B2:B9,{“>0″,”<0”})*{1,-1}**multiplies**{75,-191}**and**{1,-1}**resulting to**{75,191}**.**SUM(SUMIF(B2:B9,{“>0″,”<0”})*{1,-1})**adds**75**and**191**resulting to**266**.

## Method 3 – Apply the combination of SUMPRODUCT function and ABS function

The **SUMPRODUCT function **returns the sum of the products of corresponding ranges and arrays. The **ABS function** returns the absolute value of a number that is a number without its sign. The positive numbers remain unchanged but it converts negative numbers to positive numbers.

By using these functions together we can add the absolute values of a dataset as follows:

**Step 1** – Select **Cell B10** and enter the formula **=SUMPRODUCT(ABS(B2:B9))** as follows:

**Step 2** – Press the Enter key and the sum of absolute values will be returned:

### Explanation of the formula

`=SUMPRODUCT(ABS(B2:B9))`

**ABS(B2:B9)**returns the array of the absolute values of the numbers in the data range**B2:B9**which are**{20,10,20,12,45,56,52,51}.****SUMPRODUCT(ABS(B2:B9))**returns the sum of the absolute values.

## Method 4 – Use a combination of the SUM function and ABS function

In this method, we utilize the** SUM function** and **ABS function** in an **array formula** to generate the sum of the absolute values of a dataset.

This is done by following the steps below:

**Step 1** – Select **Cell B10 **and enter the formula **=SUM(ABS(B2:B9))** as follows:

**Step 2** – Press Enter key if you are using Excel 365, otherwise press **Ctrl + Shift + Enter** to enter the array formula. The sum of the absolute values will be displayed:

### Explanation of the formula

`=SUM(ABS(B2:B9))`

**ABS(B2:B9)**returns an array of the absolute values in the range B2:B9 which are**{20,10,20,12,45,56,52,51}**.**SUM(ABS(B2:B9))**adds the absolute values returns the value 266.

## Method 5 – Convert the negative values in place and then apply the SUM function

In this method, you convert the negative values first and then apply the SUM function to add the absolute values.

You achieve this by doing the following steps:

**Step 1** – Select **Cell D2** and key in -1 and press **Ctrl + C** to copy the value to the clipboard as follows:

**Step 2** – Press the **Ctrl** key and select all the cells containing the negative numbers you want to convert:

**Step 3** – Go to **Paste >> Paste Special…** to open the Paste Special dialog box:

**Step 4** – In the Paste Special dialog box select **Multiply** in the operation group and press **OK**:

All the negative values will be stripped of their sign:

**Step 5** – Select **Cell B10** and key in the formula **=SUM(B2:B9)** as follows:

**Step 6** – Press the Enter key and the sum of the absolute values in the range **B2:B9** will be displayed in **Cell B10**:

### Explanation of the Method

- In this method, all the negative values are first turned into positive values by multiplying them by a -1.
- The SUM function is then applied to the data range. The sum of the absolute values is then generated.

## Method 6 – Apply Excel VBA Code

Working knowledge of Excel VBA is essential for one to be able to apply this method.

In this method, we use Excel VBA code to create a **User Defined Function** or macro that will return the sum of absolute values in a data range.

This is achieved by doing the following steps:

**Step 1** – Press **Alt + F11** to open the Visual Basic Editor.

**Step 2** – To insert a new module right click your workbook in the Project window and go to **Insert >> Module**:

**Step 3** – In the new module enter the following code:

```
Function SUMABS(Rng As Range) As Double
Dim Result As Double
Dim cell As Range
Result = 0
On Error GoTo Done
For Each cell In Rng
Result = Result + Abs(cell)
Next cell
Done:
SUMABS = Result
End Function
```

**Step 4** – Save the workbook and switch to the active worksheet by pressing **Alt + F11**.

**Step 5** – Select **Cell B10** and key in the formula =SumAbs(B2:B9) as follows:

**Step 6** – Press the Enter key and the sum of absolute values in range B2:B9 will be displayed in **Cell B10**:

### Explanation of Code

- The variables
**Result**and**cell**are declared. - The
**Result**variable is initialized to 0. - The Error Handler returns 0 when there is an error in the execution of the code.
**For Each Next**loop goes through every cell in the range converting the negative values to positive ones.- The numbers are then added up and the sum is stored in the Result variable.