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
- Excel Power Query Undo: What Works and What Doesn’t
- Excel Power Query Ungroup: How to Split Grouped Rows
- Excel Power Query URL: How to Import Data from a Web Page
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- MID and FIND in Excel: Extract Text Between DelimitersTo extract text between parentheses in Excel, use MID with FIND to locate the opening and closing characters.…
- Excel Formula to List Items in a ColumnThe simplest Excel formula to list items in a column is =A1 entered in the first cell of…
- Excel Link to Named Range: Formulas and MethodsTo create a clickable link to a named range in Excel for Microsoft 365 on Windows, use the…
- Excel COUNTIF Duplicate Values Only OnceTo count duplicate values only once in Excel, you need to count each distinct value that appears more…
- Copy Values Only in Excel: Keep Results, Not FormulasTo copy results without copying their formulas in Excel for Microsoft 365 on Windows, select the cells, press…
- How to Sort Dates Correctly in Excel Without ErrorsTo sort dates correctly in Excel, make sure Excel recognizes the entries as real dates, then select a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.