SUMIFS Not Working With Blanks? Fix the Criteria

When you want to sum rows whose criteria cells are blank, use an explicit empty-string criterion: with categories in A2:A100 and amounts in B2:B100, enter =SUMIFS(B2:B100,A2:A100,""). To sum rows where those cells are not blank, use =SUMIFS(B2:B100,A2:A100,"<>"). If the result is unexpected, check whether the cells are truly empty, contain spaces, or contain formulas that display nothing.

How SUMIFS evaluates blank criteria

SUMIFS adds values in a sum range only when the corresponding cells meet every condition you specify. Its basic argument order is SUMIFS(sum_range,criteria_range1,criteria1,...). The criteria range is the set of cells Excel checks; the criterion says what those cells must match. For blank cells, the clearest criterion is the text string "", which represents a zero-length string.

For example, with category labels in A2:A7 and amounts in B2:B7, suppose A2 and A5 are genuinely empty, and their corresponding amounts are 120 and 25. The other amounts are 80, 45, 30, and 60, with nonblank labels. The formula =SUMIFS(B2:B7,A2:A7,"") returns 145: 120 plus 25. A cell containing a space is not blank, so it is not included.

Use the explicit quoted criterion rather than leaving the criterion argument out or assuming that an empty-looking cell is always equivalent to an empty string. A blank cell used as a criterion reference can behave differently from a deliberately supplied "" in some formula setups. If a user selects a blank criterion in a control cell, handle that case explicitly so the formula says what “blank” means.

Blank, empty string, zero, and space are different

Several different cell contents can look empty on a worksheet, but SUMIFS does not treat them all alike. Knowing which case you have is often the whole solution:

Truly empty cell: No value or formula has been entered. The "" criterion matches blank cells.
Formula returning an empty string: A formula such as =IF(C2=0,"",C2) can display nothing while the cell still contains a formula. Blank-related criteria often treat this as empty for matching purposes, but it is not the same as a physically empty cell.
Space or other invisible character: A cell containing a space, nonbreaking space, or other character is not truly empty. The formula may look blank but fail the "" test.
Zero: The numeric value 0 is a value, not a blank. A cell formatted to hide zero may look empty but will not match a blank criterion.

Formatting can disguise contents. To inspect a suspicious cell, select it and look at the formula bar. You can also test a cell with ISBLANK: with the cell to inspect in A2, =ISBLANK(A2) returns TRUE only when A2 is genuinely empty. It returns FALSE for a formula, even if that formula displays an empty string.

Examples for blank and nonblank rows

For the examples below, assume category labels are in A2:A100 and the numbers to add are in B2:B100. Replace those ranges with the actual ranges in your sheet, keeping them aligned and the same size.

Add amounts where the category is blank: =SUMIFS(B2:B100,A2:A100,""). This is useful for finding unassigned transactions, missing departments, or records without a status.
Add amounts where the category is not blank: =SUMIFS(B2:B100,A2:A100,"<>"). This excludes cells Excel matches as blank. Check formula-generated empty strings separately if your data mixes formulas and truly empty cells.
Sum a different measure for the same blank rows: If quantities are in C2:C100, use =SUMIFS(C2:C100,A2:A100,""). The first range is always what gets added; the criteria range can be another column.

When the criterion comes from a selector cell, decide whether an empty selector means “match blank labels” or “do not filter by label.” Those are different requests. With category labels in A2:A100, amounts in B2:B100, and selector D1, this formula treats a blank D1 as “match blank labels” and otherwise matches the selected label: =IF(D1="",SUMIFS(B2:B100,A2:A100,""),SUMIFS(B2:B100,A2:A100,D1)). If a blank selector should mean “sum everything,” use a separate all-rows branch instead; do not assume that an empty criterion means no filtering.

Why a blank-matching formula may return zero

A zero result does not always mean SUMIFS is broken. It may mean no rows satisfy the criterion, or that the rows you consider blank contain a character or value. Work through the following checks:

Confirm the correct column. Check that the criteria range contains the cells that should be blank, not an adjacent field such as a formula-generated status.
Check the sum range. The amounts must be numeric values for SUMIFS to add them. Numbers stored as text may not contribute as expected; inspect and convert the source data if necessary.
Look for spaces and invisible characters. Click a cell that appears empty and inspect the formula bar. A pasted space is enough to make it nonblank. TRIM can remove ordinary leading and trailing spaces, but it does not remove every nonprinting character or every nonbreaking space.
Check formulas and display formats. A formula may return an empty string, and a custom number format may hide zero. Both can look like a blank cell while representing different underlying contents.
Compare range dimensions. The sum range and every criteria range should cover corresponding rows. For example, pairing B2:B100 with A3:A101 misaligns the records even though both ranges have the same height.
Inspect other criteria. SUMIFS requires all supplied criteria to be true for a row. A correct blank test can still produce zero if a date, region, or status criterion excludes those rows.

To diagnose a simple case, temporarily count how many cells match the blank condition. With labels in A2:A100, =COUNTIF(A2:A100,"") counts cells matching the empty criterion. If that count is zero, inspect the cells and confirm the range before changing the sum formula. COUNTIF is a diagnostic, not proof that every match is physically empty: formulas returning an empty string can also be treated as blank by blank-counting functions.

Distinguishing genuinely empty cells from formulas returning nothing

If your rule is “sum rows only when the category cell has never been filled in,” a normal blank criterion may be too broad because it can also match formula results that display an empty string. A helper column gives you a clear test. In C2, with the category to test in A2, enter =ISBLANK(A2) and fill the formula down. This marks a genuinely empty cell TRUE and a cell containing a formula FALSE.

Then, with amounts in B2:B100 and the helper results in C2:C100, use =SUMIFS(B2:B100,C2:C100,TRUE). The formula sums only rows where the helper identifies A as truly empty. For the opposite task—excluding physically empty cells but allowing formula-generated empty strings—use FALSE as the helper criterion, then decide whether those formula rows should count based on your reporting rules.

A helper column is also easier to audit than a complicated formula when other people maintain the workbook. Label it clearly, such as “Category physically blank,” and explain why it differs from a displayed blank. If you clean the source data instead, keep a copy first and confirm that removing spaces or replacing formulas will not discard information users need.

Spaces, cleanup, and data-quality fixes

When imported data contains spaces, decide whether to clean the source or create a normalized helper column. A simple TRIM formula can remove ordinary leading and trailing spaces and reduce repeated internal spaces to one. For example, with raw labels in A2, =TRIM(A2) returns a cleaned text value in another column. An input consisting of ordinary spaces becomes an empty string in the helper result, which can then be tested consistently.

TRIM is not a universal cleanup tool. Data copied from web pages or external systems may contain nonbreaking spaces or other characters that TRIM does not remove. If the blank-looking values persist, inspect the source and use a targeted replacement or a suitable cleanup process. Avoid replacing every unusual character indiscriminately: some characters may be meaningful in names, codes, or imported identifiers.

Another source of confusion is formatting. A custom number format can hide zero values, and conditional formatting can make text difficult to see. Clear or change the format temporarily to determine whether the cell contains a value. Do not delete a value just because it looks blank; first establish whether the business rule is based on actual emptiness, a missing text label, or a zero amount.

SUMIFS versus other ways to handle blanks

For a straightforward total with one or more conditions, SUMIFS is generally the most readable option. It keeps the sum range, criteria ranges, and criteria together and can add multiple conditions without helper columns. Use SUMIF when there is only one condition and the simpler form is sufficient; it serves a similar purpose but has a different argument order.

Use a helper column when the word “blank” needs a precise definition, such as distinguishing an empty cell from a formula result. That adds a column but makes the classification visible and reusable. You can also use a PivotTable to summarize records by a category field, though blank labels may appear as their own category and the layout depends on the source data. A PivotTable is useful for exploration, while SUMIFS is often more convenient when a report needs a specific formula-driven total.

For newer Microsoft 365 workflows, FILTER can return matching rows for review, but it is not necessary just to calculate a total. SUMIFS is available in modern Excel versions and usually remains the simplest choice for this task. The best method depends on whether you need a total, an auditable classification, or a visible list of the records that match.

Frequently Asked Questions

What is the SUMIFS criteria for a blank cell?
Use "" as the criterion, as in =SUMIFS(B2:B100,A2:A100,""). The formula adds B values for rows where the corresponding A cell matches the blank criterion.

How do I make SUMIFS ignore blank cells?
Use the not-equal-to criterion "<>", for example =SUMIFS(B2:B100,A2:A100,"<>"). If the range contains formulas returning empty strings, verify how those rows are classified for your specific requirement.

Why does SUMIFS count a cell that looks blank?
It may contain a formula returning an empty string, a space, a zero hidden by formatting, or another invisible character. Inspect the formula bar and test with ISBLANK to distinguish a truly empty cell from a cell containing a formula or value.

Does SUMIFS treat zero as blank?
No. Zero is a numeric value, not an empty cell. If zero appears blank, check the number format or conditional formatting; use a criterion for zero only when you actually want to match numeric zero.

Can SUMIFS match a blank cell and another condition?
Yes. Add more criteria-range and criterion pairs. For example, require both a blank category and a particular region by adding the region range and its matching text criterion. Each criterion must be true for the row to be included.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.