The absolute value is used to present the difference between two entities. After calculation, the resultant difference may be positive or negative. The combination of the positive and negative signs makes us puzzled. For this reason, we take the absolute value of the result to get a clear idea. In this article, we will discuss how to sum absolute value in Excel.
What Is Absolute Value?
The absolute value of a number is the distance of that number from the origin in the line graph. It is the magnitude of that number. It is also called the modulus of that number. We can denote the absolute value of x as |x|.
Example: The absolute value of -2, |-2|=2.
How to Sum Absolute Value in Excel: 6 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. Use ABS Function Inside the SUM Function to Sum Absolute Value
The SUM function adds all numbers in a range of cells.
The ABS function returns the absolute value of a number, a number without its sign.
In this section, we will use the ABS with the SUM functions to get the sum. of absolute values.
📌 Steps:
- First, add a new row in the dataset to calculate the sum.
- Now, put the following formula on cell B12.
=SUM(ABS(B5:B9))Â
- Press the Enter button to get the result.
Here, the ABS function returns the absolute value of the selected range. Then, the SUM function adds all the absolute values.
Read More: Calculate Absolute Percentage Error with Excel Function
2. Get the Absolute Value of Sum Result Using SUM Inside ABS Function
Now, if you get a negative result after doing a sum operation, you can use the SUM function inside an ABS function to remove the minus sign and get the absolute value.
📌 Steps:
- Go to cell B12 and put the following formula there.
=ABS(SUM(B5:B9))
- Then, press the Enter button to get the result.
3. Combination of Two SUMIF Functions to Sum Absolute Values
Here, we will use the SUMIF function two times to get the absolute value of the selected number without using the ABS function. We will add all the positive numbers and negative numbers separately. Then, subtract the negative numbers from the positive ones.
📌 Steps:
- Now, put the below formula at cell B12.
=SUMIF(B5:B9,">0")-SUMIF(B5:B9,"<0")
- Press the Enter button now.
Read More: How to Make All Numbers Positive in Excel
4. Combination of SUM and SUMIF Functions to Sum Absolute Value
In this section, we will use the combination of the SUM and SUMIF functions. The SUMIF function adds negative and positive numbers separately. Then, apply the SUM function that multiplies the positive numbers by +1 and negative numbers by -1 and add them finally.
📌 Steps:
- For this, insert the following formula on cell B12.
=SUM(SUMIF(B5:B9,{">0","<0"})*{1,-1})Â
- Finally, press the Enter button.
Read More: Changing Negative Numbers to Positive in Excel
5. Get the Absolute Value of Sum for Specific Values Using SUMIF and ABS Functions
Here, we will combine ABS and SUMIF functions. The SUMIF function will function based on a condition. After that, the ABS function will find out the absolute value of the sum.
📌 Steps:
- Here, we have 2 lists of numbers on the dataset.
We will apply a condition within the SUMIF function. That is the sum of the positive numbers from list 1. From list 2, we will sum the negative numbers. Then, add both results and finally apply ABS operation on the previous result.
- Now, apply 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.
- Finally, press the Enter button.
Read More: Opposite of ABS Function in Excel
6. Sum Absolute Value with a Function in Excel VBA
In this section, we will use the VBA macro code to sum absolute value. We will make a customized function using the VBA code to do this.
📌 Steps:
- First, 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.
- Put 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
- Now, go to cell B12.
We can see the newly created function appears here.
- After inserting the whole function choose the range from the dataset. So, the formula becomes:
=ABS_SUM(B5:B9)
- Finally, press the Enter button.
Similar Readings
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, we described 6 ways to sum absolute value in Excel. We also added a VBA code to perform the task. I hope this will satisfy your needs. Please give your suggestions in the comment box, we will get back to you as early as possible.
