[Fixed!] Excel Not Adding Negative Numbers Correctly (4 Solutions)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Not Adding Negative Numbers Correctly

  • Now, right-click on any of the selected cells and select Paste Special.

Excel Not Adding Negative Numbers Correctly

  • Next, in the Paste Special window, select Values from the Paste options and click OK.

Excel Not Adding Negative Numbers Correctly

  • 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 Add Negative Numbers 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))

Excel Not Adding Negative Numbers Correctly

  • 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.

Using SIGN Function for Adding Negative Numbers Separately

Steps:

  • To start this method, click on cell C5 and type in the following formula:
=SIGN(B5)

Using SIGN Function for Adding Negative Numbers Separately

  • Now, you will see the value 1 in cell C5 which denotes that there is a positive number in cell B5.

Using SIGN Function for Adding Negative Numbers Separately

  • Next, drag the Fill Handle to copy the formula of cell C5 to all the cells below.

Using SIGN Function for Adding Negative Numbers Separately

  • 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.

Adding Negative Numbers Using Excel AutoSum Feature

  • 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


<< Go Back to Negative Numbers in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo