Formula Errors

How to Fix SUMIFS Not Including All Values in Excel Tables

Khadija Khan Sep 28, 2026 869 views

Question details

The SUMIFS function fails to calculate or include all matching rows in an Excel table.

How to Fix SUMIFS Not Including All Values in Excel Tables

Product: Microsoft Excel
Device & OS: not provided
Scenario: Calculating totals across different tables (like Initial, Extra, and Total registers) using SUMIFS, especially when rows are added manually or via Power Automate.
Observed behavior: The SUMIFS formula misses certain rows due to inconsistent spaces in lookup values (like UserIDs) or because new rows fail to inherit the table's formulas properly.

Before you start

Before troubleshooting, ensure your data is formatted as an official Excel Table (Insert > Table) and that calculation options under the Formulas tab are set to Automatic.

Solution 1Recommended

Remove Trailing Spaces from Lookup Values

Inconsistent spaces or hidden characters in criteria (like UserIDs) prevent SUMIFS from finding exact matches.

When data is imported or added via automation tools, it often contains hidden trailing spaces. SUMIFS looks for an exact match, so "ID123" and "ID123 " will be treated as different values.

Identify inconsistent data

Check your lookup columns (e.g., UserID) in both the source table and the total table for accidental trailing spaces by clicking into the formula bar.

Use the TRIM function

Create a temporary helper column next to your data and use the formula =TRIM(A2) to remove extra spaces from the text.

Paste as values

Copy the newly cleaned data from the helper column, right-click the original column, and select 'Paste Special > Values' to overwrite the inconsistent data.

Remove Trailing Spaces from Lookup Values

Wildcard alternative: If you cannot alter the source data immediately, you can append a wildcard character in your SUMIFS criteria, such as criteria & "*", to match values regardless of trailing spaces.

Solution 2

Verify Table Formula Auto-Expansion

When new rows are added (manually or via tools like Power Automate), the SUMIFS formula must automatically fill down to include them in the calculation.

Fix SUMIFS and Clean Data Efficiently with WPS Spreadsheet

WPS Spreadsheet offers powerful data cleaning tools and full support for advanced functions like SUMIFS, ensuring your calculations are always accurate. It is highly compatible with Microsoft Excel file formats and provides a smooth automated table experience.

1. Open your workbook: Launch WPS Spreadsheet and open your existing .xlsx file containing the tables and SUMIFS formulas.
2. Clean trailing spaces: Use the built-in =TRIM() function or the 'Text to Columns' feature under the Data tab to eliminate inconsistent spaces in your criteria columns.
3. Recalculate formulas: Once the data is cleaned, WPS Spreadsheet will automatically update your SUMIFS totals. Ensure 'Automatic Calculation' is enabled under the Formulas tab.
4. Manage table ranges: If you add new data manually, simply select the table and drag the bottom-right corner indicator to include the new rows in your SUMIFS range.

Fully compatible with Microsoft Excel (.xlsx) formulas, tables, and structured references.Built-in text cleaning tools to quickly remove spaces and hidden characters.Seamless table auto-expansion for automated data entries.Lightweight, fast, and completely free to use for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS returning zero when there is matching data?

This usually happens when the criteria data types do not match (e.g., numbers stored as text) or there are hidden characters and trailing spaces in the cells. Use the TRIM or CLEAN functions to standardize your data before calculating.

How do I make SUMIFS include newly added rows automatically?

Format your data range as a standard Table (Ctrl+T). When you use structured references (like Table1[Amount]) in your SUMIFS formula instead of fixed ranges (like A1:A100), the formula will automatically include any newly appended rows.

Can SUMIFS handle wildcard characters for partial matches?

Yes, you can use an asterisk (*) for multiple characters or a question mark (?) for a single character in your criteria. For example, using "*"&A2&"*" as your criteria will sum values that contain the text in A2 anywhere in the target range.

Excel & Data Problems