Fix SUMIFS Returning Different Totals in Filtered Excel Tables
Question details
The user needs to resolve an issue where SUMIFS or SUMPRODUCT formula results do not match the visible total of a filtered table.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating conditional totals on a dataset that has active table filters applied.
- Observed behavior
- The SUMIFS formula returns a total that differs from the visible data because hidden filters or unmatched criteria are included in the calculation.
Ensure that your table headers are visible and check if there are any manually hidden rows that might be affecting your data view independently of the applied filters.
Match Formula Criteria with Active Table Filters
Align your SUMIFS formula criteria with the active filters in your table to ensure both reflect the exact same data subset.
SUMIFS and SUMPRODUCT functions evaluate all data in the selected range, including rows that are hidden by filters. If a filter is applied to the table but not explicitly included as a criterion in your formula, the formula will include the hidden rows in its total, causing a discrepancy.
Look at the header row of your Excel or WPS Spreadsheet table. Columns with an active filter will display a funnel icon instead of the standard dropdown arrow.
Click the funnel icon on each filtered column to see which specific items are checked. Compare these active filter conditions against the criteria arguments in your SUMIFS formula.
Add additional criteria ranges and criteria to your SUMIFS formula so that it perfectly matches the active filters. Alternatively, clear the unneeded table filters from the Data tab if you want the table to reflect the formula's total.

Calculate and Filter Data Accurately with WPS Spreadsheet
WPS Spreadsheet provides robust data analysis capabilities, including advanced filtering and formula calculations like SUMIFS and SUBTOTAL. It allows you to easily identify active filters and ensures your visible data aligns perfectly with your formula results.
- 1. Open Your Data: Launch WPS Spreadsheet and open your existing workbook containing the filtered table.
- 2. Inspect Filters: Navigate to the Data tab on the top ribbon and verify which columns have the AutoFilter funnel icon activated.
- 3. Apply Formulas: Use the formula bar to edit your SUMIFS criteria or implement the SUBTOTAL function to sum only visible cells effortlessly.

Frequently Asked Questions
Why does SUMIFS calculate hidden rows?
The SUMIFS function is designed to evaluate all cells in the specified range that meet your defined criteria, regardless of their visibility status. It does not ignore rows hidden by manual hiding or auto-filters.
How do I sum only visible rows in a filtered table?
To sum only visible rows, use the SUBTOTAL function with function number 109. For example, typing '=SUBTOTAL(109, B2:B50)' will sum the values in column B while automatically ignoring any filtered or hidden rows.
What causes SUMPRODUCT to mismatch with filtered data?
Like SUMIFS, the SUMPRODUCT function evaluates the entire array of data including hidden rows. If your table is filtered but your SUMPRODUCT formula doesn't use an array multiplier to specifically exclude hidden rows (like nesting SUBTOTAL within SUMPRODUCT), the totals will differ.




