Formula Errors

How to Fix SUMIF Structured References in an Excel Table

Amos Gikunda Sep 28, 2026 869 views

Question details

The user needs to understand why the @ symbol appears in table structured references and how to properly construct a SUMIF formula that evaluates an entire table column rather than just the current row.

Fix SUMIF Structured References in an Excel Table

Product: Spreadsheets
Device & OS: not provided
Scenario: Writing a SUMIF formula within an Excel table using structured references for data summarization.
Observed behavior: The formula limits the reference to the current row (indicated by the @ symbol) instead of spanning the entire column needed for the criteria and sum ranges.

Before you start

Verify that your data range is officially formatted as a Table (Insert > Table) and take note of the specific Table Name and Column Headers you intend to reference in your formula.

Solution 1Recommended

Remove Implicit Intersection (@) for Entire Column References

Use this solution to adjust your formula syntax so that the criteria range and sum range span the whole column, while only the criteria references the current row.

In Excel and WPS Spreadsheets, structured references make table formulas easier to read. However, clicking a single cell in the same row automatically adds an '@' symbol, representing 'implicit intersection' (only the current row's value).

To perform a SUMIF calculation across the entire table, you must ensure the '@' symbol is omitted for the criteria range and the sum range.

Define the Criteria Range

Begin your formula by typing =SUMIF(. For the first argument (criteria range), select the entire target column by clicking the top edge of the column header. The formula should look like Table12[Short Description] without the @ symbol.

Set the Row-Specific Criteria

Add a comma. For the second argument (criteria), click the cell in the current row that contains your condition. The system will automatically insert the @ symbol, looking like [@[Short Description]]. This is correct because you want to evaluate against the specific row's value.

Define the Sum Range

Add a final comma. For the third argument (sum range), select the entire column containing the values you want to add up. This should also omit the @ symbol, appearing as Table12[Amount($)].

Finalize and Apply

Close the parenthesis so your final formula matches the structure: =SUMIF(Table12[Short Description], [@[Short Description]], Table12[Amount($)]). Press Enter to apply it down the table.

Remove Implicit Intersection (@) for Entire Column References

Quick Selection Tip: When highlighting ranges with your mouse, wait for your cursor to turn into a downward-pointing black arrow at the top of the table column. Clicking once selects the data body (entire column without headers), which correctly generates the [ColumnName] reference.

Master Table Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced structured references, making it incredibly simple to build dynamic, readable formulas like SUMIF without syntax headaches.

1. Format Data as Table: Highlight your dataset, go to the Home tab, and click 'Format as Table'. Check 'My table has headers'.
2. Start Formula with Auto-complete: Type =SUMIF( in your desired cell. As you type your table's name, WPS Spreadsheet will provide a helpful dropdown to quickly select columns.
3. Select Ranges Intuitively: Simply click the top of the column to insert the full column reference, or click a single cell to automatically insert the @ symbol for row-level criteria.

100% format compatibility with Microsoft Excel table structures and formulasIntuitive formula auto-complete prevents referencing errorsFree and lightweight alternative for powerful data analysis and reportingSeamless integration with PivotTables and charts

microsoft office alternative - wps office

Frequently Asked Questions

Why does the @ symbol automatically appear in my formula?

The @ symbol denotes an 'implicit intersection'. It tells the spreadsheet program to only look at the value in the exact same row as the formula, rather than evaluating the entire column. It appears automatically when you click a single cell within a table column during formula creation.

Can I manually type structured references instead of clicking?

Yes. You can manually type the table name followed by brackets, such as Table1[ColumnName]. Modern spreadsheet software will usually color-code the reference to confirm you have typed a valid table and column name.

How do I find the name of my table?

Click any cell inside your table. A new 'Table Design' or 'Table Tools' tab will appear on your ribbon. On the far left of this tab, you will see a 'Table Name' box where you can view or rename your table.

Does this structured reference rule apply to other functions like COUNTIF?

Yes, the exact same rules apply. When using functions like COUNTIF, AVERAGEIF, or VLOOKUP, you should omit the @ symbol when referencing the range to evaluate the whole column, but keep the @ symbol when pointing to a specific row's criteria.

Excel & Data Problems