Author: Oscar Cronquist Article last updated on December 06, 2017

This article explains why your formula is not working properly, there are usually four different things that can go wrong. The first one is numbers stored as text, demonstrated in the picture above.

Why is the SUM function in cell C7 returning 0 (zero) in the picture above? The numbers in cell range C3:C6 are stored as text. You can see that by the green arrow in each cell's top left corner.

Why is this happening? The cell range C3:C6 was formatted as text cells before you entered the numbers in these cells.

How to solve it? Select cell range C3:C6, click on exclamation mark symbol (or exclamation point if you are American) to open a menu. Click on "Convert to Number", the green arrows disappear and the SUM function formula works as expected again.

Manual calculation

The SUM formula in cell C7 returns 0 (zero), why is this happening?

Check if your workbook is in manual calculation mode. Go to tab "Formulas", then click on "Calculations Options" button.

In this case, the setting was on "Manual", changing it back to "Automatic" makes the SUM formula work as intended again.

Show formula

Cell C7 displays the formula, not the result. Why is this happening?

Go to tab "Formulas", check if "Show Formulas" button is enabled (highlighted).

Click on the "Show Formulas" button to disable it. The cell now shows the result of the SUM calculation.

Formula cell formatted as text

The cell in C7 shows the formula? I don't want that, I want to see the calculation result.

Select cell C7, press CTRL + 1 on your keyboard.

This reveals that the cell is formatted as "Text" making it show the formula and not the result. Click on "General" and then on OK button.

Now it shows the output from the formula.