Sum absolute values
This tutorial shows how to sum absolute values from a range using Excel formulas, with the SUMPRODUCT, ABD and SUMIF functions, or VBA
Example: Sum absolute values
|
=SUMPRODUCT(ABS(B5:B9))
|
This formula uses the Excel SUMPROCUT and ABS functions to sum the absolute values from range (B5:B9).
|
METHOD 2. Sum absolute values with SUMIF function
EXCEL
|
This formula uses the Excel SUMIF function to sum all the positive and negative numbers, separately. It then converts the negative numbers into positive by putting the negative (-) in front of the formula that sums the negative numbers, which is then added to the positive numbers to return the sum of absolute values.
|
Sub Sum_Absolute_Values()
'declare variables
Dim ws As Worksheet
Dim Rng As Range
Dim ws As Worksheet
Dim Rng As Range
Set ws = Worksheets("Analysis")
Set Rng = ws.Range("B5:B9")
Set Rng = ws.Range("B5:B9")
Result = 0
'loop through each cell in the range to convert the number into an absolute value and sum
For Each cell In Rng
For Each cell In Rng
Result = Result + Abs(cell)
Next cell
ws.Range("E4") = Result
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Date Range: Select the range from which you want to sum absolute value by changing the range reference ("B5:B9") in the VBA code.
Worksheet Selection: Select the worksheet, by changing the Analysis worksheet name in the VBA code, which captures a range of cells from which you want to sum absolute values. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("E5") in the VBA code.
Date Range: Select the range from which you want to sum absolute value by changing the range reference ("B5:B9") in the VBA code.
Worksheet Selection: Select the worksheet, by changing the Analysis worksheet name in the VBA code, which captures a range of cells from which you want to sum absolute values. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial shows how to sum absolute values from a range using Excel formulas or VBA.
This tutorial provides two Excel method that can be applied to sum absolute values by using either a combination of the SUMPRODUCT and ABS functions or using the SUMIF functions.
This tutorial provides two Excel method that can be applied to sum absolute values by using either a combination of the SUMPRODUCT and ABS functions or using the SUMIF functions.
This tutorial provides one VBA method that can be applied to sum absolute values from a range. It loops through each cell in the specified range, converts it to an absolute value and adds to the running total.
FORMULA (using the SUMPRODUCT and ABS functions)
=SUMPRODUCT(ABS(range))
=SUMPRODUCT(ABS(range))
FORMULA (using the SUMIF function)
=SUMIF(range, ">0")-SUMIF(range, "<0")
=SUMIF(range, ">0")-SUMIF(range, "<0")
ARGUMENTS
range: The range of cells from which you want to sum absolute values.
range: The range of cells from which you want to sum absolute values.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Count only positive numbers | How to count cells that contain only positive numbers using Excel and VBA methods | |
| Count only negative numbers | How to count cells that contain only negative numbers using Excel and VBA methods | |
| Count cells if greater than | How to count cells that are greater than a specific value using Excel and VBA methods | |
| Count cells if less than | How to count cells that are less than a specific value using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMIF Function | The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria | |
| SUMPRODUCT Function | The Excel SUMPRODUCT function multiplies corresponding ranges and returns the sum of these values | |
| ABS Function | The Excel ABS function returns the absolute value of a number |