Skip to main content

How to sum the absolute values in Excel?

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?

arrow blue right bubble Sum the absolute values with formulas

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.

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (2)
No ratings yet. Be the first to rate!
Katty
Katty
This comment was minimized by the moderator on the site
Thanks. it works!!!!
Reply
0
0
Gerard Veneman
Gerard Veneman
This comment was minimized by the moderator on the site
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.
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations