Nazmul Hossain Shovon
In this tutorial, I am going to share with you 4 possible solutions to the problem of excel not adding negative numbers correctly. These solutions are quick and also easy to apply. Although adding numbers in Excel is one of the easiest operations, having negative numbers can pose challenges if you do not know how to overcome them. In the following sections, I am going to guide you through some simple steps to overcome this problem.
Negative Numbers in Excel
Excel can work with both positive and negative numbers easily and effectively. But in some cases, negative numbers can cause some problems. Choosing the wrong format for negative numbers is a common cause of errors. There are certain best practices that will allow Excel to recognize the negative numbers.
4 Possible Solutions If Excel Is Not Adding Negative Numbers Correctly
The dataset that we will be using, shows the cash flow records. The positive cash flows indicate money that is coming as revenue. The negative numbers denote money that is going out as expenses.
1. Converting Negative Numbers with Paste Special Option
We can sum both positive and negative numbers in Excel. But sometimes Excel fails in adding negative numbers correctly because of the wrong format that is set for them. In this method, we will separate the values of the numbers to solve the problem.
Steps:
- First, select the cells from C5 to C9.
- Then, press Ctrl+C to copy the cell data.
- Now, right-click on any of the selected cells and select Paste Special.
- Next, in the Paste Special window, select Values from the Paste options and click OK.
- As a result, all the cells will now have only numeric values.
- Here, double-click on cell C10 and type the following formula:
=SUM(C5:C9)
- Finally, you should get the sum of all the positive and negative numbers from the formula.
Read More: How to Change Positive Numbers to Negative in Excel
2. Adding Negative Numbers Ignoring Signs
In many situations, we may want to ignore the number signs when excel is not adding negative numbers correctly. We can do this by combining the SUM function and the IF function. See the following steps.
Steps:
- To begin with, double-click on cell C10 and insert the following formula:
=SUM(IF(C4:C9<0,-1*C4:C9,C4:C9))
- Consequently, Excel will ignore any negative sign and add all cell data.
Read More: How to Show Negative Numbers in Excel
3. Using SIGN Function for Adding Negative Numbers Separately
We can use the SIGN function to denote the negative numbers when excel is not adding them correctly. In the dataset for this method, we will sum the positive and negative values in separate cells. Follow the steps below.
Steps:
- To start this method, click on cell C5 and type in the following formula:
=SIGN(B5)
- Now, you will see the value 1 in cell C5 which denotes that there is a positive number in cell B5.
- Next, drag the Fill Handle to copy the formula of cell C5 to all the cells below.
- After that, double-click on cell F6 and enter the below formula:
=SUMIF(C5:C10,C5,B5:B10)
- Next, press Enter and this will sum all the positive values having the sign as 1.
- Then, double-click on cell F7 and insert the following formula:
=SUMIF(C5:C10,C8,B5:B10)
- Here, press Enter and excel will sum all the negative values with the sign as -1.
Read More: How to Put a Negative Number in Excel Formula
4. Adding Negative Numbers Using Excel AutoSum Feature
The SUM function might not always give accurate results when dealing with negative numbers. In this case, we may try using the AutoSum feature to fix the problem of excel not adding negative numbers correctly. Let us see how we can do that.
Steps:
- First, click on cell C10.
- Now, go to the Formula tab and then to AutoSum.
- Here, click on Sum.
- Finally, you should get the total value considering the negative numbers.
Read More: How to Count Negative Numbers in Excel
Things to Remember
- In some situations, you might want to conditionally format the cells with negative numbers which will make them more visible.
- The arithmetic operations that excel performs on negative numbers are the same as with positive numbers.
- There are various ways of representing negative numbers in excel including using parentheses.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
I hope that you were able to solve the problem where Excel is not adding negative numbers correctly using the above fixes. You can try these fixes one by one and see which one best suits your situation. If you have any queries, please let me know in the comments.
Related Articles
- How to Add Brackets to Negative Numbers in Excel
- Excel Formula to Return Zero If Negative Value is Found
- Excel Negative Numbers in Brackets and Red
- How to Make a Group of Cells Negative in Excel
- How to Make Negative Numbers Red in Excel
- How to Convert Negative Value to Positive in Excel Using Formula
- Excel Formula If Cell Contains Negative Number
- Excel Formula for Positive and Negative Numbers
