Formula Errors

How to Fix Excel Structured SUM Formula Returning #REF Error

Muhammad Talha Oct 9, 2026 869 views

Question details

The user needs to fix a #REF! error that occurs when using a structured SUM formula in an Excel table.

How to Fix Excel Structured SUM Formula Returning #REF! Error

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating row totals across multiple columns in an Excel table using a structured formula like =SUM([@Monday]:[@Sunday]).
Observed behavior: The formula returns a #REF! error in an existing workbook, even though the exact same formula functions perfectly when tested in a newly created workbook.

Before you start

Verify the exact name of the table you are working in, and check if your workbook contains any other sheets or tables that might have unusual names.

Solution 1Recommended

Resolve Table Naming Conflicts and Use Explicit References

Identify and rename any tables or named ranges that conflict with standard Excel functions, then update your formula to use a full explicit table reference.

In some cases, if another table or named range in your workbook is given a name that matches an Excel function (such as a localized version of 'SUM'), it causes the calculation engine to misinterpret the structured reference, resulting in a #REF! error. Renaming the conflicting table and specifying the exact table name in your formula resolves this.

Open the Name Manager

Navigate to the 'Formulas' tab on the Excel ribbon and click on 'Name Manager' (or press Ctrl + F3 on your keyboard) to view all defined names and tables in the workbook.

Rename Conflicting Tables

Scroll through the list to look for any table or named range that shares a name with an Excel function. Select it, click 'Edit', and give it a unique name (e.g., change 'SUM' to 'WeeklyData').

Identify Your Target Table Name

Click any cell inside the table where you are experiencing the error. Go to the 'Table Design' tab and note the 'Table Name' displayed in the properties group on the far left.

Apply Explicit Table Reference

Select the formula cell and rewrite the formula to include the specific table name. For example, if your table is named 'Table1', enter `=SUM(Table1[@[Monday]:[Sunday]])` and press Enter.

Resolve Table Naming Conflicts and Use Explicit References

Tip: Using explicit references like TableName[@[Column1]:[Column2]] ensures the software always knows exactly which table's data is being calculated, preventing reference drop-offs when formulas are moved.

Calculate Table Data Seamlessly

Manage Structured Formulas Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for structured table references, making it easy to sum rows and columns without complex referencing errors. It efficiently handles advanced data structures and named ranges.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your table data.
2. Format your data as a table: Select your data range, navigate to the 'Home' tab, and click 'Format as Table' to easily manage structured references.
3. Manage named ranges: Go to the 'Formulas' tab and click 'Name Manager' to ensure there are no table names conflicting with formula names.
4. Enter the explicit formula: In your total column, type `=SUM(TableName[@[Monday]:[Sunday]])` and hit Enter to calculate the row sum instantly and accurately.

Fully compatible with Microsoft Excel structured table references and advanced formulas.Built-in Name Manager helps easily track and resolve conflicting data ranges.Lightweight and fast calculation engine, even with complex multi-sheet workbooks.Free to use for everyday spreadsheet tasks with a highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

What does a #REF! error mean in Excel?

A #REF! error means that a formula refers to a cell, range, or table that is not valid. This frequently happens if referenced cells were deleted or if a naming conflict confuses the software's calculation engine.

Why does my structured formula work in a new workbook but not the existing one?

Existing workbooks often accumulate hidden named ranges, conflicting table names across different sheets, or corrupted references that interfere with how formulas are interpreted. A new workbook has a clean state without these conflicts.

How do I find or change the name of my Excel table?

Click anywhere inside your table, then navigate to the 'Table Design' (or 'Design') tab on the top ribbon. Look at the 'Table Name' input box on the far left, where you can view or edit the current table name.

What is an explicit table reference?

An explicit table reference includes the actual table name before the column or row identifiers, such as Table1[@[Column1]:[Column2]]. This precise targeting prevents confusion if the formula is evaluated outside the table boundaries or if multiple tables exist.

Excel & Data Problems