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?

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**.- C
**lick**on the**error icon**. - Select
**Convert to Number**from the**Context menu**.

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

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

**Check Delimited**.- Press
**Next**.

- Mark
**Tab**and press**Next**.

- Mark
**General**in the**last window**. - Press
**Finish**to confirm**.**

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

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

- 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
**Fill Handle**tool to copy the formula throughout column D**.**

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

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

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

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

- [Fixed!] Excel Formulas Not Working on Another Computer
- How to Sum Range of Cells in Row Using Excel VBA
- [Solved:] Excel Formula Not Working unless Double Click Cell
- How to Sum Columns in Excel
- [Solved]: Excel Array Formula Not Showing Result
- How to Sum Only Positive Numbers in Excel
- Shortcut for Sum in Excel

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.

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.

really helpful your blog

really helpful your blog buddy

Hello,

Tanael Wawe!You are welcome. Stay in touch with

ExcelDemyto get more helpful content.Regards

ExcelDemyreally helpful your blog buddy…. love you

Hello,

Tanael Wawe!Thanks for your appreciation. Stay in touch with

ExcelDemyto get more helpful content.Regards

ExcelDemyHello,

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:

TEXTfunction to add leading zeroNow, there could be a few reasons why the

SUMfunction in Excel is failing to deliver any results for your data. Here are some recommendations to aid in troubleshooting the issue:NumberfromHometab the problem can still persist.Trace Errorbutton will appear. Click on that button and selectConvert to Number.CLEANfeature to get rid of any concealed characters. For instance, if your data is in cellA1and 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.#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

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.

Dear

JessThanks for your comment! Only leading spaces typically don’t affect the

SUMfunction 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(usingSUM,VALUE, andSUBSTITUTEfunctions):`=SUM(VALUE(SUBSTITUTE($B$2:$B$7, " ", "")))`

Hopefully, you will like the solution. Good luck.

Regards

Lutfor Rahman ShimantoExcelDemyNone of these helped me. I tried several of these solutions. Don’t understand.

Maybe this spreadsheet has a bug.

BEGINNING BALANCE 07/23 4,460,522.16

INCOME

REAL ESTATE/PROPERTY TAX 1,523,670.50

DELIQUENT PROPERTY TAX 35,502.78

TANGIBLE PROPERTY TAX 244,420.81

MOTOR VEHICLE TAX 263,771.33

RAILROAD/FRANCHISE TAX 52,602.07

TELECOMMUNICATION TAX 9,788.79

WATERCRAFT TAX 17,917.57

AIRCRAFT TAX 153.76

UNMINED MINERALS –

INTEREST 174,397.28

CD Interest 144,566.24

TOTAL INCOME $-

TOTAL INCOME PLUS CARROVER $-

Hello

Cathy ThompsonThanks for visiting our blog! We cannot provide an ultimate solution without reviewing your Excel file and being remote. However, you apply several adjustments to the dataset, like checking for non-numeric characters, converting text to numbers, ensuring correct formatting, and checking calculation mode. To clean up your numeric data, you can create a helper numeric column (e.g., real estate tax) and use the formula:

`=VALUE(TRIM(CLEAN(B1)))`

Lastly, sum the values in the helper columns using theSUMfunction.Hopefully, these ideas will help. If you still have difficulties, you can share your problem with the

ExcelDemy Forumby attaching your Excel file. Good luck.Regards

Lutfor Rahman ShimantoExcelDemy