Fixing Excel SUMIF That Returns Wrong Total When Criteria Range Has Hidden Rows

You applied a filter to your dataset, scrolled down to your summary cell, and the SUMIF total hasn't budged. Or worse, it has changed but in a way that makes no sense relative to what's visible on screen. This is one of the most confusing Excel surprises because the formula looks completely correct — and it is correct, just not for the task you're asking it to do.

SUMIF does not care whether rows are hidden. It never has. Once you understand that, the fixes become straightforward.

What you'll learn

• Why SUMIF sums hidden rows even when they aren't visible
• How to reproduce and confirm the problem reliably
• Four practical fixes, from SUMPRODUCT to helper columns
• How to choose the right fix for your specific workbook
• Pitfalls that cause the replacement formulas to break

What's actually happening under the hood

Excel's SUMIF function operates on the raw cell values in its criteria range and sum range. It has no concept of row visibility. When you filter or manually hide rows, Excel marks those rows with an internal hidden flag, but SUMIF's evaluation loop simply iterates every row in the specified range, hidden or not, and applies the criteria check against the raw value.

This is by design, not a bug. SUMIF was built to answer the question: "Sum all values in this range that match this condition" — full stop. Visibility is a display property, not a data property from Excel's perspective.

The distinction matters because Excel does have functions that respect row visibility: SUBTOTAL and AGGREGATE both skip hidden or filtered rows depending on the function number you pass them. SUMIF is simply not in that family.

Why hidden rows don't affect SUMIF the way you expect

Consider a column of sales amounts next to a column of region names. You filter the sheet to show only "North" region rows. Visually, you see five rows. Your SUMIF uses the region column as the criteria range, checks for "North", and sums the matching amounts. You'd expect it to return the sum of those five visible rows.

But if other hidden rows also contain "North" as their region value, SUMIF includes them too. The filter hid them from view; it didn't exclude them from calculation. Your total is inflated by every hidden "North" row in the dataset.

The reverse scenario also bites people: you manually hide a group of rows that contain values matching your criteria, then check your SUMIF summary and wonder why the total is higher than the visible rows justify. Same root cause.

This problem has a close cousin covered in detail in the article on fixing AVERAGEIF errors when all cells are filtered out — the entire SUMIF/AVERAGEIF/COUNTIF family behaves this way.

Reproducing the problem in three steps

If you want to confirm this is your issue before changing any formulas, do this:

1. In a blank column next to your data, enter =SUBTOTAL(103,A2) in the first data row and copy it down the entire dataset. This returns 1 for visible rows and 0 for hidden rows.
2. Note the value your SUMIF currently returns.
3. Temporarily clear all filters (Data → Clear). If the SUMIF value doesn't change, your criteria genuinely don't match any hidden rows. If it does change, you've confirmed hidden rows are being included.

The helper column from step 1 will also be useful if you choose Fix 3 below.

Fix 1: Use SUMPRODUCT with explicit visibility logic

SUMPRODUCT is the most flexible fix because it lets you inject a visibility test directly into the formula. The trick is using SUBTOTAL(103, ...) inside an array context to detect whether each row is visible.

=SUMPRODUCT(
  (criteria_range="YourCriteria")*
  SUBTOTAL(103, OFFSET(criteria_range_first_cell, ROW(criteria_range)-ROW(criteria_range_first_cell), 0))*
  sum_range
)

That's dense, so here's a concrete example. Suppose your region names are in B2:B100 and your sales amounts are in C2:C100:

=SUMPRODUCT(
  (B2:B100="North")*
  SUBTOTAL(103, OFFSET(B2, ROW(B2:B100)-ROW(B2), 0))*
  C2:C100
)

The SUBTOTAL(103,...) part returns a 1 for every visible row and 0 for every hidden row. Multiplying by it zeroes out any contribution from hidden rows before summing. This works for both AutoFilter and manually hidden rows when you use function number 103 (COUNTA variant that respects both).

One important note: this formula can be slow on very large ranges because it evaluates SUBTOTAL once per row. On datasets under 50,000 rows it's generally fine. For larger sets, consider Fix 2.

Fix 2: Use AGGREGATE for a cleaner syntax

The AGGREGATE function (available in Excel 2010 and later) supports conditional summing of visible rows with a cleaner interface. However, AGGREGATE doesn't natively support conditional sums the way SUMIF does — so you still need SUMPRODUCT to apply the criteria filter, but you can simplify the visibility check.

A practical pattern is to use AGGREGATE to build a visible-rows-only sum range, then combine it with your criteria logic:

=SUMPRODUCT(
  (B2:B100="North")*
  (SUBTOTAL(103, OFFSET(B2, ROW(B2:B100)-ROW(B2), 0))=1)*
  C2:C100
)

This is functionally equivalent to Fix 1 but makes the visibility condition explicit (=1), which some people find easier to read and audit. If you're working in Excel 365, you can also use the dynamic array-aware version with FILTER:

=SUM(FILTER(C2:C100, (B2:B100="North")*(SUBTOTAL(103,OFFSET(B2,ROW(B2:B100)-ROW(B2),0))=1)))

The FILTER approach is more readable and recalculates efficiently, but it requires Excel 365 or Excel 2021. Earlier versions will return a #NAME? error.

Fix 3: Use helper columns to mark visible rows

If your formulas are already complex and you don't want to make them harder to maintain, a helper column is the pragmatic solution. Add a column alongside your data with this formula in each row:

=SUBTOTAL(103, B2)

Copy it down through your entire dataset (replace B2 with any cell in the same row). This column returns 1 when the row is visible and 0 when it's hidden or filtered out. Now your conditional sum becomes a standard SUMIFS with an extra criterion:

=SUMIFS(C2:C100, B2:B100, "North", D2:D100, 1)

Where column D contains the helper formula. This is easy to understand, easy to audit, and works in any Excel version that supports SUMIFS (Excel 2007+). The downside is that it adds a column to your sheet, which may not suit every workbook layout.

This helper-column approach is the same pattern that solves range-mismatch issues discussed in the article on SUMIF returning zero due to a sum range offset mismatch — keeping your ranges explicit and aligned prevents a whole class of formula errors.

Fix 4: Switch to a structured Excel Table with SUBTOTAL

If your data is already formatted as an Excel Table (Insert → Table), you get an automatic "Total Row" that uses SUBTOTAL by default. This respects the active filter on the table and only sums visible rows.

The catch is that the Total Row gives you an unconditional sum — it sums everything visible, not just rows matching a specific criterion. If you need conditional sums inside a table environment, combine the Table's structured references with SUMPRODUCT:

=SUMPRODUCT(
  (Table1[Region]="North")*
  SUBTOTAL(103, OFFSET(Table1[[#Headers],[Region]], ROW(Table1[Region])-ROW(Table1[[#Headers],[Region]]), 0))*
  Table1[Sales]
)

Structured references make this more readable than raw cell addresses, and if your table grows with new rows, the formula automatically includes them without any changes.

When each fix is the right choice

Common pitfalls when summing visible rows

Using function number 9 instead of 103 in SUBTOTAL

SUBTOTAL(9,...) sums only AutoFilter-hidden rows — it ignores rows hidden manually via Format → Hide Rows. If you need both to be excluded, always use SUBTOTAL(109,...) for SUM or SUBTOTAL(103,...) for COUNTA-based visibility detection. The 100-series variants respect both filter-hidden and manually-hidden rows.

OFFSET spanning a different number of rows than your data range

The OFFSET construct inside SUMPRODUCT must match the exact dimensions of your criteria and sum ranges. If your data starts at row 2 and your OFFSET anchors to a different row, the visibility array will be misaligned and you'll get wrong results silently. Double-check that ROW(range) - ROW(first_cell) gives you a zero-based offset starting at 0.

Forgetting that FILTER returns an array, not a single value

In Excel 365, if your criteria match zero visible rows, FILTER returns a #CALC! error rather than zero. Wrap it with IFERROR(..., 0) to handle empty results gracefully: =IFERROR(SUM(FILTER(...)), 0).

Criteria using wildcards or numeric comparisons

When you replace SUMIF with SUMPRODUCT, the criteria syntax changes. SUMIF accepts ">100" as a string criterion. In SUMPRODUCT you write it as (C2:C100>100) — a direct Boolean expression. Wildcards like "North*" still work inside SUMPRODUCT when you use ISNUMBER(SEARCH("North",B2:B100)) instead. This is a common source of silent errors when migrating away from SUMIF. For more on related date-criteria issues that surface in the same family of functions, see the article on COUNTIFS returning wrong counts with date range criteria.

Volatile functions slowing down large workbooks

OFFSET is a volatile function — it recalculates every time anything in the workbook changes, not just when its dependencies change. In large workbooks, putting OFFSET inside a SUMPRODUCT that spans thousands of rows can cause noticeable lag. If performance is an issue, the helper column approach (Fix 3) is much faster because SUBTOTAL(103, single_cell) is evaluated once per cell rather than in a large array.

If you're debugging other silent errors in Excel's conditional functions, the article on INDEX MATCH returning #N/A with merged cells covers another class of formula failures that are easy to misdiagnose.

Wrapping up

SUMIF including hidden rows is expected behavior, not a bug — but that doesn't mean you're stuck with wrong totals. Here are your concrete next steps:

• Confirm the problem by temporarily clearing all filters and checking whether your SUMIF total changes.
• If you're on Excel 365, replace SUMIF with =IFERROR(SUM(FILTER(sum_range, (criteria_range="value") * (SUBTOTAL(103, OFFSET(...))=1))), 0) for readable, dynamic results.
• If you're on an older Excel version, add a helper column using =SUBTOTAL(103, B2) and switch to SUMIFS with that column as an extra criterion.
• If you use SUBTOTAL anywhere in your fix, verify you're using the 100-series function numbers to handle both AutoFilter and manually hidden rows.
• Test your fixed formula by hiding and un-hiding a few rows manually and checking that the total updates correctly each time.