[Fixed!] Excel Not Adding Negative Numbers Correctly (4 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.


Download Practice Workbook

You can download the practice workbook from here.


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

Many times 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)

Read More: How to Change Positive Numbers to Negative in Excel (6 Ways)


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 (4 Quick Tricks)


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 Easy Methods)


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 (3 Easy Ways)


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.

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. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon

Nazmul Hossain Shovon

Hello, I am Nazmul Hossain. I am currently working full-time in Exceldemy as an Excel & VBA Content Developer. I have completed my bachelors in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. I am interested in working with MS Excel. I also like coding web applications a lot.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo