Formula Errors

Fix Excel SUM Returning a #FIELD! Error

Bushra ParveenBushra Parveen Nov 20, 2026 868 views

Question details

Correct an Excel SUM formula that returns a #FIELD! error.

Fix a #FIELD! Error in an Excel SUM Formula
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to add a range of cells with the SUM function.
Observed behavior
A formula such as =SUM(B2.B5) returns #FIELD! because the period is interpreted as field access rather than a range separator.
Before you start

Select the cell with the error and check its formula in the formula bar. Identify the first and last cells you want to add, such as B2 and B5.

Solution 1Recommended

Replace the period with a colon in the SUM range

Use a colon to add every cell between the first and last cell in the range.

1
Edit the formula

Select the cell showing #FIELD!, then click the formula bar or press F2.

2
Correct the range

Replace =SUM(B2.B5) with =SUM(B2:B5). Alternatively, type =SUM(, select B2 through B5, and type the closing parenthesis.

3
Calculate the result

Press Enter. The formula should add the numeric values in B2 through B5.

Replace the period with a colon in the SUM range
Use a colon for ranges: A period can indicate access to a linked data type field; it does not define a range. Check that the formula contains B2:B5, not B2.B5.
Calculate ranges in WPS Spreadsheets

Sum a cell range in WPS Spreadsheets

Use WPS Spreadsheets to open a workbook and add a range with the same colon-based SUM formula. Linked data type features may differ from Excel.

  1. 1. Open the workbook: Open WPS Spreadsheets and choose Open to select your Excel workbook.
  2. 2. Select a result cell: Click the cell where you want the total to appear.
  3. 3. Enter the formula: Type =SUM(B2:B5), replacing B2:B5 with your desired range, and press Enter.
  4. 4. Save the workbook: Choose File > Save or Save As to save your changes in an Excel-compatible format.
Add a range with a familiar SUM formulaOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Why does =SUM(B2.B5) return #FIELD!?

The period can be interpreted as a linked data type field reference, not a range separator. In the formula bar, change it to =SUM(B2:B5).

What if =SUM(B2:B5) still returns an error?

Check B2 through B5 for error values and correct their source formulas. If a cell contains linked data, open its data card and verify any referenced field names.

How can I enter the range without typing a colon?

In the result cell, type =SUM(, drag to select B2 through B5, type ), and press Enter. Excel inserts the range reference for the selected cells.

Do I need to type @ before SUM?

No. For this calculation, enter =SUM(B2:B5). The @ character is not required to create a basic SUM formula.