Calculation Issues

Fix SUMIFS Returning Different Totals in Filtered Excel Tables

Maira MehtabMaira Mehtab Oct 10, 2026 872 views

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.

How to Fix SUMIFS Returning Different Totals in Filtered Tables
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.
Before you start

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.

Solution 1Recommended

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.

1
Check Active Filters

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.

2
Compare Filters with Formula

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.

3
Update the 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.

Match Formula Criteria with Active Table Filters
Alternative Function: If your goal is to always sum only the visible rows regardless of complex criteria, consider using the SUBTOTAL(109, range) or AGGREGATE(9, 5, range) function instead of SUMIFS.
Powerful Spreadsheet Tool

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. 1. Open Your Data: Launch WPS Spreadsheet and open your existing workbook containing the filtered table.
  2. 2. Inspect Filters: Navigate to the Data tab on the top ribbon and verify which columns have the AutoFilter funnel icon activated.
  3. 3. Apply Formulas: Use the formula bar to edit your SUMIFS criteria or implement the SUBTOTAL function to sum only visible cells effortlessly.
Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Clear visual indicators for active filters to prevent calculation mismatchesFree, lightweight, and easy-to-use interface for seamless data analysis
microsoft office alternative - wps office

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.