SUMIF Blank and Not Blank Cells in Excel

To SUMIF blank cells, use two double quotes as the criteria: =SUMIF(D2:D8,"",C2:C8) adds the numbers in column C on every row where column D is empty. For cells that are not blank, use "<>" instead. This guide is for anyone who tracks unpaid invoices, open orders or missing dates and needs a total for the rows that still have a gap.

How to sum if a cell is blank#

Use "" as the criteria to match empty cells. SUMIF checks one column for blanks and adds the matching numbers from another column. All examples use this invoice list in A1:D8:

To total the unpaid invoices, the ones with no payment date:

=SUMIF(D2:D8,"",C2:C8)

The result is 1,920 (300 + 980 + 640). The two double quotes with nothing between them mean "empty". The SUMIF function guide explains each argument in more detail.

How to sum if a cell is not blank#

Use "<>" as the criteria to match cells that contain something. The <> operator means "not equal to", and with nothing after it, "not equal to empty":

=SUMIF(D2:D8,"<>",C2:C8)

The result is 860 (450 + 125 + 210 + 75), the invoices that have a payment date. The same operator excludes a specific value, such as "<>North"; see SUMIF not equal to for those patterns.

Check the result: blank plus not blank equals the total#

Every row is either blank or not blank in the checked column, so the two SUMIFs must add up to the full total:

=SUM(C2:C8)

This returns 2,780, and 1,920 + 860 = 2,780. If your two results don't add up to the total, some cells only look empty. See the section on fake blanks below.

How to sum blanks with another condition (SUMIFS)#

When you need a blank test and another condition, switch to SUMIFS. Remember that SUMIFS puts the sum range first. To total Lior's unpaid invoices:

=SUMIFS(C2:C8,D2:D8,"",B2:B8,"Lior")

The result is 1,620 (980 + 640). Rowan has no unpaid invoices, so the same formula with "Rowan" returns 0.

You can mix blank checks with any other criteria. For example, unpaid invoices over 500: =SUMIFS(C2:C8,D2:D8,"",C2:C8,">500") returns 1,620. For more number conditions, see SUMIF greater than, and for partial text matches, see SUMIF contains text.

How to total blanks for each client in a summary table#

To get an unpaid total for every client at once, point the criteria at a cell and lock the ranges. Type the client names Rowan, Kemi and Lior in F5:F7, then in G5 type:

=SUMIFS($C$2:$C$8,$D$2:$D$8,"",$B$2:$B$8,F5)

Select G5:G7 and fill down (Windows: Ctrl+D; Mac: ⌘D). You get 0 for Rowan, 300 for Kemi and 1,620 for Lior, which adds up to the 1,920 unpaid total.

The $ signs keep the three ranges fixed on rows 2 to 8 as you fill. F5 has no $, so it moves to F6 and F7. Press F4 on Windows (or ⌘T on a Mac) while the cursor is in a reference to add the $ signs. Don't put F5 in quotes: "F5" would look for the letters F and 5, not the client's name.

Sum if blank with ISBLANK and SUMPRODUCT#

ISBLANK returns TRUE only for a cell that is truly empty. Combined with SUMPRODUCT, it gives the same total without SUMIF:

=SUMPRODUCT(ISBLANK(D2:D8)*C2:C8)

This returns 1,920 on the invoice data. ISBLANK turns each Paid on cell into TRUE or FALSE, multiplying turns those into 1 or 0, and SUMPRODUCT adds the amounts where the result is 1.

The difference shows up with formulas. If column D held a formula such as =IF(B2="","",…) that returns an empty string, SUMIF with "" treats that cell as blank, but ISBLANK says FALSE, because the cell holds a formula. Choose the one that matches what "blank" means in your sheet. The lesson on IS functions lets you test ISBLANK, ISNUMBER and ISTEXT on real cells.

Why SUMIF blank gives the wrong total#

Most problems come from cells that look empty but aren't. Check these:

1. A space in the cell. A cell with one space is not blank, so "" skips it and "<>" counts it. Click the cell and look at the formula bar. Clear such cells, or use Find and Replace to remove spaces.
2. An apostrophe or invisible character from an import. Select the column and press Delete on the cells that should be empty.
3. Ranges of different sizes. D2:D8 with C2:C9 can line up the wrong rows. Make both ranges start and end on the same rows.
4. Blanks in the numbers column instead. SUMIF already treats an empty Amount as 0. You only need "" when the blank is in the column you check.
5. Blanks that should be filled. Sometimes a blank means "same as above", for example in exported reports. Fill them first; see how to fill blank cells in Excel.

To count blank rows instead of adding them, =COUNTIF(D2:D8,"") returns 3. And if SUMIF itself is new, start with the guide to using SUMIF.

Practise it#

Try the exercise on this page: it totals the unpaid invoices with a blank criteria. Then practise SUMIF in a live spreadsheet, with and without a separate sum range, and practise ISBLANK and the other IS functions to check what's really in a cell. Each formula is checked as soon as you press Enter.