[Fixed!] Excel SUM Formula Is Not Working and Returns 0 (3 Solutions)

To explore the solutions, use the following dataset that represents some ordered Smartphones and their Quantity.


Issue 1 – Number Stored as Text

Have a look that I have used the SUM function here to find the total quantity but it returned zero. Why is it happening?

Number Stored As Text

The reason is that I stored the numbers as text values. That’s why there are green triangular icons in every cell. So the SUM formula recognized no numbers and that’s why returned zero.

Read More: Sum If a Cell Contains Text in Excel


Solution 1 – Use Convert to Number

Steps:

  • Select the cells.
  • Click on the error icon.
  • Select Convert to Number from the Context menu.

Use Convert to Number if the SUM formula is not working and returns 0

The values will be changed to numbers and the SUM formula should work properly.

Use Convert to Number if the SUM formula is not working and returns 0


Solution 2 – Apply Text to Columns Wizard

Steps:

  • Select the cells C5:C9.
  • Go to Data, then Data Tools, then Text to Columns.
  • Soon after a dialog box will open up.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

  • Check Delimited.
  • Press Next.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

  • Mark Tab and press Next.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

  • Mark General in the last window.
  • Press Finish to confirm.

Apply Text to Columns Wizard if the SUM formula is not working and returns 0.

Then should provide the proper output from the SUM formula.

Read More: Sum to End of a Column in Excel


Solution 3 – Apply Paste Special Command

Steps:

  • Copy any blank cell.

Apply Paste Special Command if the SUM formula is not working and returns 0.

  • Select the cells and right-click.
  • Choose Paste Special from the Context menu.

Apply Paste Special Command if the SUM formula is not working and returns 0.

  • In the Paste Special dialog box, mark All from the Paste section and Add from the Operation section.
  • Finally, just press OK.

This should fix the issue.


Solution 4 – Use the VALUE Function

Steps:

  • Add a helper column D.
  • Write the following formula in Cell D5:
=VALUE(C5)
  • Hit Enter.

Use the VALUE Function if the SUM formula is not working and returns 0.

  • Use the Fill Handle tool to copy the formula throughout column D.

Use the VALUE Function if the SUM formula is not working and returns 0.

  • Use the SUM formula on the helper column and you should get the expected result.

Read More: [Fixed!] SUM Formula Not Working in Excel


Issue 2 – Wrong Calculation Mode

If you keep the calculation mode in Manual mode then it might be a reason and for that Excel SUM formula is not working and returns zero. While this doesn’t happen in Excel 365, it can occur in some earlier versions.

Solution:

Always keep the calculation mode to Automatic:

  • Click as on Formulas
  • Go to Calculation Options
  • Select Automatic from the drop-down.

Change Calculation Mode if the SUM formula is not working and returns 0.

Read More: [Fixed!] Why Formula Is Not Working in Excel


Issue 3 – Non-Numeric Characters

If the cells contain non-numeric characters with the numbers then also you will get zero from the SUM formula.

Remove Non-numeric Characters if the SUM formula is not working and returns 0.

Solution:

You can remove them manually but it’s not feasible for a large dataset. If you know which character is the problem, using the Find and Replace tool will work.

Steps:

  • Select the data range C5:C9.
  • Press Ctrl + H to open the Find and Replace.
  • For the example, type comma (,) in the Find what box and keep the Replace with box empty.
  • Press Replace All.

Remove Non-numeric Characters if the SUM formula is not working and returns 0.

The tool removes all commas (in the example) and the SUM formula has worked.

Read More: How to Sum Selected Cells in Excel


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

13 Comments
  1. None of these things work (I tried them all and then some). If I highlight the cells, the summary ribbon at the bottom correctly calculates the sum. But SUM and SUMPRODUCT return zero.

    I’ve turn auto calculate off and on

    • Hello, HPOTTER.
      Thanks for your feedback. We think your problem is very specific which is difficult to identify without the file. So, if you would share your Excel file with us then we could find out the issue and hope, we could give you a solution.

  2. So these did not help, but my issue as it turned out is the data had an added space after the number, and excel did not know how to handle it.

    • Hello JK,
      Thanks for your feedback. Your problem is quite rare and unique. So it’s difficult to detect this type of problem without the user’s Excel file. If you would share your file with us, then hopefully we could detect the issue and could give you the exact solution. But temporarily we are suggesting you use the SUM function within the TRIM function, we are showing you a sample formula:
      =TRIM(SUM(C5:C9))

      The TRIM function will remove all extra spaces. I hope, it will help you.

  3. really helpful your blog

  4. really helpful your blog buddy

    • Hello, Tanael Wawe!

      You are welcome. Stay in touch with ExcelDemy to get more helpful content.

      Regards
      ExcelDemy

  5. really helpful your blog buddy…. love you

    • Hello, Tanael Wawe!

      Thanks for your appreciation. Stay in touch with ExcelDemy to get more helpful content.

      Regards
      ExcelDemy

  6. Hello,

    I applied all steps but They didnot solve my 0 problem. My data are like following form in my excel sheet:
    0.8020772933959961
    15.836971521377563
    0.20077300071716309

    but when I use sum, then I get null! Any offer will be appreciated. thx

    • Hello MAHIN,

      Greetings! We appreciate you contacting us and leaving a comment on our Excel blog post with your query. We appreciate your interest and are available to help.

      First of all, you have inserted a leading zero by any of the following means:

      • Converting a number into a Text for adding the leading zero
      • Using the TEXT function to add leading zero

      Now, there could be a few reasons why the SUM function in Excel is failing to deliver any results for your data. Here are some recommendations to aid in troubleshooting the issue:

      • The cell is previously formatted as Text. After converting them into the Number from Home tab the problem can still persist.

      • Try to copy your cells and paste them into the new destination. After pasting it in the new cells Trace Error button will appear. Click on that button and select Convert to Number.

      • Here is the final output after converting it to a number.

      • Look for hidden characters or spaces: On occasion, Excel may fail to recognize values in the cells as numbers because of hidden characters or trailing spaces. Use the CLEAN feature to get rid of any concealed characters. For instance, if your data is in cell A1 and you want to clean it up, you can use the formula “=CLEAN(A1)” in another cell, and then use the SUM function on the cleaned values.
      • Look for any mistakes in other cells: The SUM function may occasionally return null if other cells in the range you’re trying to sum include errors like #VALUE! or #DIV/0!. Examine the other cells in the range, and correct any mistakes you find.

      You should be able to fix the Excel SUM function returning null problem using the abovementioned techniques. I’ll be pleased to help you further if you can give me more information or a sample of your Excel sheet if the issue continues.

      Regards

      Al Ikram Amit

      Team ExcelDemy

  7. None of them work but I solved the problem. I noticed a blank space before the number. I used Find and Replace. Find the space and replace it with nothing. It is now adding it.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Jun 13, 2024 at 10:33 AM

      Dear Jess

      Thanks for your comment! Only leading spaces typically don’t affect the SUM function in most Excel versions. However, leading spaces can indeed cause numbers to be treated as text in some Excel versions like yours. It is great to hear that removing leading spaces resolved the issue for you.

      I am sharing another exciting solution for summing numbers with inconsistent spaces using an Excel formula. Please check the following:

      Excel Formulas (using SUM, VALUE, and SUBSTITUTE functions): =SUM(VALUE(SUBSTITUTE($B$2:$B$7, " ", "")))

      Hopefully, you will like the solution. Good luck.

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo