Excel SUMIF Does Not Include? Hidden Gaps Explained

Formulas & functions · Updated November 22, 2026

Excel’s SUMIF function does not include cells that fail the criteria you specify, but more specifically, it does not include cells from hidden rows, filtered-out rows, or cells with errors, text, or blank values that don’t match the criteria. It also cannot sum across multiple sheets or handle multiple conditions without extra functions. This article explains exactly what SUMIF excludes, why it behaves that way, and what to use instead.

How SUMIF Works: The Basic Rules

SUMIF adds values in a range based on a single condition. The syntax is:

=SUMIF(range, criteria, [sum_range])

For example, with products in A2:A10 and sales in B2:B10, =SUMIF(A2:A10,"Apple",B2:B10) sums only the sales where column A equals “Apple”. The function evaluates each cell in the range against the criteria; if the cell matches, the corresponding sum_range cell is added. If sum_range is omitted, Excel sums the range itself.

Key exclusions happen at the evaluation level:

  • Cells that do not match the criteria are ignored.
  • Blank cells in the range are treated as not matching (unless the criteria is “”).
  • Text values in sum_range are treated as zero, but only if they are not errors.
  • Error values in sum_range (like #N/A or #DIV/0!) cause SUMIF to return an error, not ignore them.

What SUMIF Does Not Include: Hidden and Filtered Rows

A common surprise is that SUMIF does not respect filtering or hidden rows. If you filter a table to show only certain rows, SUMIF still sums all rows that match the criteria, including the hidden ones. The same applies if you manually hide rows. This is because SUMIF operates on the raw cell references, not the visible dataset.

To sum only visible filtered cells, you need the SUBTOTAL function with function number 109 (SUM) or the AGGREGATE function with option 5. For example, =SUBTOTAL(109,B2:B100) sums only visible cells in that range, but it does not apply criteria. To combine criteria with filtering, you can use a helper column with a formula like =SUBTOTAL(103,A2) to mark visibility, then use SUMIFS on that helper column.

Criteria That SUMIF Cannot Handle

SUMIF only supports one condition. If you need two or more criteria, you must use SUMIFS, which has a different syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, to sum sales for “Apple” in the “North” region, you would write =SUMIFS(C2:C100, A2:A100, "Apple", B2:B100, "North").

Another limitation: SUMIF cannot use cell references to entire columns with criteria that are not exact matches unless you use wildcards. It supports wildcards like * (any sequence) and ? (single character). For instance, =SUMIF(A2:A10,"A*",B2:B10) sums sales for any product starting with “A”. But it cannot handle criteria based on another cell’s value that is a formula result unless you reference that cell directly, like =SUMIF(A2:A10, D1, B2:B10).

Common Problems and Misconceptions

One frequent misconception is that SUMIF ignores errors in the sum_range. It does not—if any cell in sum_range contains an error, SUMIF returns that error. To avoid this, you need to use IFERROR within a SUMPRODUCT or array formula. For example, =SUMPRODUCT((A2:A10="Apple")*(IFERROR(B2:B10,0))) works but must be entered as a regular formula (no Ctrl+Shift+Enter needed in modern Excel).

Another issue: text numbers. If your criteria range contains numbers stored as text, SUMIF may not match numeric criteria. For instance, if A2:A10 has “123” as text and your criteria is 123 (number), SUMIF returns 0. Use =SUMIF(A2:A10,"123",B2:B10) or convert the text to numbers.

Also, SUMIF does not include cells from other sheets unless you reference them directly in the range argument. You cannot use a 3D reference like =SUMIF(Sheet1:Sheet3!A2:A10,"Apple",Sheet1:Sheet3!B2:B10)—that will return a #VALUE! error. You must sum each sheet separately or use SUMPRODUCT with INDIRECT.

Alternatives: SUMIFS, SUMPRODUCT, and Dynamic Arrays

For most cases, SUMIFS is the better choice because it handles multiple criteria and is more flexible. If you need to sum based on OR logic (e.g., “Apple” or “Banana”), you can use SUMPRODUCT with addition: =SUMPRODUCT((A2:A10="Apple")+(A2:A10="Banana"),B2:B10).

For Excel 365, you can use the FILTER function to create a dynamic array of matching values and then sum them: =SUM(FILTER(B2:B10,A2:A10="Apple")). This also respects hidden rows only if you use the optional argument, but it does not ignore errors unless you wrap it in IFERROR.

How to Choose the Right Function

Follow this quick decision guide:

  • One condition, no filtering, no errors → use SUMIF.
  • Multiple conditions (AND logic) → use SUMIFS.
  • Need to ignore hidden/filtered rows → use SUBTOTAL or AGGREGATE, possibly with a helper column.
  • Need to ignore errors or use OR logic → use SUMPRODUCT.
  • Need a dynamic spill range → use FILTER + SUM.

Frequently Asked Questions

Why does SUMIF return 0 when there are visible matches? Check if the criteria range has numbers stored as text, or if the criteria itself is text vs. number. Also, ensure the sum_range is correctly aligned—if you omit sum_range, Excel sums the range, which may not be what you intend.

Does SUMIF work on filtered data? No, SUMIF ignores filtering and hidden rows. It sums all cells that match the criteria, regardless of visibility. Use SUBTOTAL or AGGREGATE for visible-only sums.

Can I use SUMIF with a cell reference for criteria? Yes, you can use a cell reference like =SUMIF(A2:A10, D1, B2:B10). If D1 contains text, it works as a direct match. For partial matches, you need to concatenate wildcards: =SUMIF(A2:A10,"*"&D1&"*",B2:B10).

Why does SUMIF return #VALUE! when I use multiple sheets? Because SUMIF does not support 3D references. You must reference each sheet separately or use SUMPRODUCT with INDIRECT, but that can be volatile. Better to use SUMIFS with multiple ranges on the same sheet.

How do I sum cells that are not blank? Use =SUMIF(A2:A10,"<>",B2:B10) to sum where A is not blank. To sum where B is not blank, swap the range and sum_range: =SUMIF(B2:B10,"<>",B2:B10).

Does SUMIF ignore error cells in the criteria range? No, if the criteria range contains an error, SUMIF will return that error. You need to use IFERROR with SUMPRODUCT or an array formula to skip errors.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.