Excel SUM Formula Not Adding Correctly: How to Fix

As a spreadsheet application, Excel is one big calculator that also displays numeric data in tables and graphs. The vast majority of Excel users will need to utilize the application’s SUM function (or formula). As the SUM function adds numbers up, it’s probably Excel’s most widely utilized formula.

The SUM function will never miscalculate numbers. However, SUM function cells don’t always display the expected results.

Sometimes they might display error messages instead of numeric values. In other cases, SUM formula cells might just return a zero value. These are a few ways you can fix an Excel spreadsheet not adding up correctly.

Why is the Excel SUM formula not adding correctly?

There are several reasons why the SUM formula in Excel might not be adding correctly. Some possible causes include:

• The formula is using the wrong cell references: Make sure you are using the correct cell references in your formula, and that the cells being referenced are actually part of the data you want to sum.
• There are hidden rows or columns that are being included in the calculation: If you have hidden rows or columns in your data, make sure they are not being included in the calculation by accident. You can check this by looking at the cell references in the formula and seeing if they include any hidden cells.
• There are cells with text or errors in them: If there are cells in the range being summed that contain text or errors, the SUM formula will not work correctly. You can fix this by making sure all cells being summed contain only numerical data.
• The cell format is set to text: If the cell format is set to text, the SUM formula will not work correctly. You can fix this by selecting the cells you want to sum, and then going to the Home tab in the ribbon and clicking on the “Number” dropdown in the “Number” group. Choose “General” or another number format from the list.
• The formula has been entered incorrectly: Make sure you are using the correct syntax for the SUM formula, including the equal sign (=) and the open and close parentheses.

How can I fix Excel SUM functions that don’t add up?

1. Check the syntax of the SUM function

First, check you’ve entered the SUM function in the formula bar with the right syntax.

The syntax for the SUM function is:

=SUM(number 1, number 2)

Number 1 and number 2 can be a cell range, so a SUM function that adds the first 10 cells of column A will look like this: =SUM(A1:A10). Make sure there are no typos of any description in that function that will cause its cell to display a #Name? error as shown directly below.

2. Delete spaces from the SUM function

The SUM function does not add up any values when there are spaces in its formula. Instead, it will display the formula in its cell.

To fix that, select the formula’s cell; and then click in the far left of the function bar. Press the Backspace key to remove any spaces before the ‘=’ at the beginning of the function. Make sure there aren’t spaces anywhere else in the formula.

3. Widen the formula’s column

If the SUM formula cell displays #######, the value might not fit within the cell. Thus, you might need to widen the cell’s column to ensure the whole number fits.

To do that, move the cursor to the left or right side of the SUM cell’s column reference box so that the cursor changes to a double arrow as in the snapshot directly below. Then hold the left mouse button and drag the column left or right to expand it.

4. Remove text formatting from cells

The Excel SUM function will not add up any values that are in cells with text formatting, which display text numbers on the left of the cell instead of the right side. To ensure all cells within a SUM formula’s cell range have general formatting. To fix this, follow these steps.

1. Select all the cells within its range.
2. Then right-click and select Format Cells to open the window shown directly below
3. Select General in the Category box.
4. Click the OK button.
5. The cells’ numbers will still be in text format. You’ll need to double-click each cell within the SUM function’s range and press the Enter key to switch its number to the selected general format.
   
6. Repeat the above steps for the formula’s cell if that cell displays the function instead of a value.

5. Select the Add and Values Paste Special options

Alternatively, you can add values in cells with text formatting without needing to convert them to general with this trick.

1. Right-click an empty cell and select Copy.
2. Select all the text cells with the function’s cell range.
3. Click the Paste button.
4. Select Paste Special to open the window shown directly below.
5. Select the Values and Add options there.
6. Click the OK button. Then the function will add the numbers in the text cells.

Those are a few of the best ways you can fix Excel spreadsheet formulas that aren’t adding up correctly.

In most cases, the SUM function doesn’t add up because it hasn’t been entered right or because its cell reference includes text cells.

If you know of any other way to fix this Excel-related issue, leave a message in the comment section below so other users can try it.