Supposing you have a list of data which contains both positive numbers and negatives, and now you want to sum their absolute values which means all the negatives will be calculated as positives. For example, you want 10+(-10) would be 20 instead of 0. Are there any quick tricks to help you calculate the absolute values in Excel?
Here are some cute formulas for you to sum the absolute values, please look at this:
Formula 1: SUMIF() function
With the following two SUMIF functions, you can quickly calculate the sum of the absolute values, do as this:
1. In a blank cell which you want to put the result, enter one of below formulas, and press the Enter key.
=SUMIF(A2:A14,">0") - SUMIF(A2:A14,"<0")
=SUM(SUMIF(A2:A14,{">0","<0"})*{1,-1})
Formula 2: SUMPRODUCT() function
This Sumproduct formula also can do a favor for you, please type the following formula, and press Enter key, then you will get the sum of the absolute values that you need.
=SUMPRODUCT(ABS(A2:A14))
Formula 3: SUM() Array Formula
If you are interested in the array formula, I can talk about this Sum array formula. Please enter below formula, and press Ctrl+Shift+Enter keys to get the sum.
=SUM(ABS(A2:A14))
Sum the absolute values by changing all negative numbers to positive in Excel
Apart from formulas, we can change all negative numbers to positive ones and then sum up these numbers. And you can apply Kutools for Excel's Change Sign of Values utility to get it done with ease.
Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now
1. Select a blank cell below the numbers whose absolute values you will add, type the formula =SUM(A2:A14) (A2:A14 is the number cells whose absolute values you will sum) into it, and press the Enter key. See screenshot:
2. Select these number cells (A2:A14 in our case), and click Kutools > Content > Change Sign of Values.
3. In the opening Change Sign of Values dialog box, please check the Change all negative values to positive option, and click the Ok button.
And now all negative values are converted to positive values, and the summing result changes automatically.
Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now
Sum the absolute values an amazing tool
You can also apply the Sum Absolute Value formula of Kutools for Excel to solve the problem easily.
Kutools for Excel- Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required!Get It Now
1. Select a blank cell you will place the sum at, and click Kutools > Formula Helper > Formula Helper.
2. In the Formula Helper dialog, please select Math from the Formula Helper drop-down list, click to highlight Sum absolute values in the Choose a formula list box, specify the range of numbers whose absolute values you will sum, and click the Ok button.
Now you get the sum of selected numbers' absolute values.
Related articles:
Sum only filtered or visible cells in Excel Maybe it is easy for you to sum a column of numbers in Excel, but sometimes you need to filter or hide some data to meet your criteria. After hiding or filtering, and now you just want to add up only the filtered or visible values. If you apply the Sum function in Excel, all the values including the hidden data will be added, in this case, how could you sum only the filtered or visible cell values in Excel?
Sum every other or nth row/column in Excel As we all know, we can apply the Sum function to add a list of cells, but sometimes we need to sum every other cell for some purpose, and Excel has no standard function allow us to sum every nth cell. In this situation, how could we sum every other or nth row / column in Excel?
Count / sum odd / even numbers or rows in Excel If there are range of numbers in your worksheet which contain both odd numbers and even numbers, but now, you need to count or sum only the odd numbers or even numbers. How could you finish this task quickly in Excel worksheet?
Count and sum cells based on background color in Excel Supposing you have a range of cells with different background colors, such as red, green, blue and so on, but now you need to count how many cells in that range have a certain background color and sum the colored cells with the same certain color. In Excel, there is no direct formula to calculate Sum and Count of color cells, here I will introduce you some ways to solve this problem.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
There's another method. You could use a macro-function too. A macro function is entered holding CTRL+SHIFT while pressing ENTER. The formula needed for the examples showed above is:
{=SUM(ABS(A2:A11))}
You should not type the accolades { and }. They will appear automatically when you hold the CTRL and SHIFT keys while pressing ENTER. Excel uses this to indicate the formula is a macro-formula.
