Fix a SUM Formula That Totals to Zero in Apple Numbers

Usually, this happens because the spreadsheet is formatting numbers as text. If it sees it as text, you can't derive a sum from it.

You're trying to sum numbers in Apple Numbers only to find that the total comes to zero? Clearly, your total shouldn't be zero. So, what gives? Typically, there is one main reason this anomaly happens. In this post, I'll explain why a sum formula that totals to zero in the first place and a way to fix it every time.

Things to Know

• When a SUM formula shows a total of 0, this means the spreadsheet is not recognizing numbers you have in the spreadsheet as actual numbers. The spreadsheet is reading them as 'text'.
• In order to fix this, you'll need to convert the 'text' entries to 'number' values. Using a VALUE function can accomplish this, but sometimes you'll also need to use a CLEAN function beforehand.
• A SUM formula that totals to zero is not common, so you don't have to worry about it happening randomly or all of a sudden. Usually, this issue happens when data is downloaded from an external source (like a server) and opened into a spreadsheet. Entires from the server aren't properly recognized as a number when opened in a spreadsheet, thus causing the issue.

Why Does my SUM Formula Total to Zero?

Nothing can be more confusing than summing a set of numbers only to have them total to zero. The reason this is happening: the spreadsheet doesn't recognize the entries in the cells as a "number". Chalk this up to formatting. The contents of the cell is formatted as "text" (or perhaps something else). Thus, the spreadsheet can't SUM text. Instead of getting an error message (the red triangle with a white explanation in it), the sum formula comes back as zero since it has "technically summed" what's within the selected cells. From the spreadsheet point of view, there is no error.

Why is SUM to Zero Happening in First Place?

So, how are my numbers get formatted as text? Generally speaking, this does not happen randomly nor would it happen within a spreadsheet you created by scratch. Usually, it's because the information contained within the spreadsheet was downloaded from a database from a server.

Some examples of a download form a database:

• You downloaded data from an Amazon seller product breakdown
• You queried data from some sort of enterprise platform and the output came as is
• A martian from the planet Zerbannon was responsible

Whatever the case may be, the fix will be the same each and every time.

Fix a SUM Formula that Totals to Zero

In order to fix a sum formula that totals to zero, you need to convert the entries in your cells from a 'text' format to a 'number' format. Unfortunately, changing the format "Data Format" drop down from 'Text' to 'Numbers' won't work (you could always try this first, but I've not seen it work since the issue is deeper than surface level formatting).

A Spreadsheet Example To Start

Here's how to fix a SUM formula that totals to zero.

In the screenshot below, I have an example of what looks like numbers. However, notice the SUM formula totals to zero.

How You'll Know Which Cell Entires are 'Text'

Upon closer inspection, we can see that these 'numbers' are not actually a number. In the next screenshot, when selecting cell A2, we can see that the info bar on the bottom of the spreadsheet says 'Text'. 

Another way to confirm the entry format, when selecting Cell tab in the side bar, we can see the Data Format also lists the format as 'Text'.

Lastly, look at the alignment. By default, alignment of 'Text' is always to the left of the cell. Also, by default, alignment of numbers are to the right of the cell. Notice, in the screenshot below, the number is aligned to the right. This is another visual clue that the format is 'Text' and not 'Number'.

IMPORTANT NOTE: Changing the Data Format to Numbers or Currency typically will NOT work.

The Fix: Step 1 of 2 (CLEAN FUNCTION - Potentially Optional)

The first thing we need to do is to "clean" the format of the information in the cell. When data is downloaded from a database, there could be "invisibles" that are also included. This is to say there may be more formatting contained within the cell than you can visually see such as a carriage return (that's hitting the 'return' key on your keyboard). These "invisibles" may or may not apply to your spreadsheet, but it's good to be potentially aware of them.

I like to start with a CLEAN function before moving on to the actual fix, just in case. Otherwise, the actual fix may not work because of any "invisible" formatting. In the screenshot below, when I click within the cell, you can see there is actually a carriage return just before the number as indicated by the cursor.

To fix, create another column next to Column A. From there, use the CLEAN function as shown in the screenshot below. Without going into too much detail of the CLEAN function, you essentially are removing certain text or formatting characters contained within the cell (this includes any carriage returns).

The CLEAN function only requires 1 input, so enter the function and select the corresponding cell next to it. Do this for all the affected cells within your spreadsheet.

Even though we have now "Cleaned" the cells, the sum still goes to zero. In the next step, we will finally resolve this.

The Fix: Step 2 of 2 (VALUE FUNCTION)

The final step will be to convert your text to a value. To do this, use the VALUE function. Again, create another column next to your data. In our example, it will now be Column C. With the VALUE function, you are basically formatting the cell contents as a number.

The VALUE function only requires 1 source-string, so enter the function in Column C and select the corresponding cell next to it. For us, we'll want to use Column B, not Column A since Column A wasn't in an acceptable format to begin with.

For your spreadsheet, you may be able to use the VALUE function from the start. Once you enter your formula, copy it all the affected cells within your spreadsheet.

Notice how the spreadsheet can now properly sum the numbers in Column C. It can now recognize the data in the cell as a number format.

Conclusion and Takeaways

If you're using a SUM formula that totals to zero instead of the actual sum it's should be, it's very likely the cells involved have been formatted as 'Text'. You can't fix this by simply changing the Data Format from 'Text' to 'Number'.

To convert the cell entry to a 'Number', use the VALUE function. You may also need to "clean" the original data beforehand in case there are any "invisible" characters you can't see included within the cell. To do this, use the CLEAN function first and then use.