Sum Absolute Value in Excel

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:

1. Use two SUMIF functions
2. Employ the combination of the SUM function and SUMIF function
3. Apply the combination of the SUMPRODUCT function and ABS function
4. Use the combination of the SUM function and ABS function
5. Convert the negative values in place and then use the SUM function
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

1. SUMIF(B2:B9,”>0″) adds all the positive numbers and returns the value 75.
2. SUMIF(B2:B9,”<0″) adds all the negative numbers and returns the value -191.
3. The negative value is then subtracted from the positive value: 75-(-191). This results in 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

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

Method 3 – Apply the combination of the 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

1. 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}.
2. 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

1. 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}.
2. SUM(ABS(B2:B9)) adds the absolute values and 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

1. In this method, all the negative values are first turned into positive values by multiplying them by a -1.
2. 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:

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

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