Cellwise Try it free

Excel guide · 4 min read

SUMIF not blank: sum if the cell next to it is not empty

Quick answerUse =SUMIF(D2:D9,"<>",C2:C9) to add the amounts in C2:C9 whose cell in column D is not blank. "<>" on its own means "not empty". For the opposite, =SUMIF(D2:D9,"",C2:C9) adds the amounts where column D is blank.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDE
1InvoiceClientAmountPaid onRegion
2INV-2041Harbor Dental1250Oct 6, 2026North
3INV-2042Mill Lane Bakery480South
4INV-2043Ostrander Law2300Oct 14, 2026North
5INV-2044Pike & Sons760 South
6INV-2045Greenway Gyms1980Oct 21, 2026South
7INV-2046Larkfield School615North
8INV-2047Nova Print340Oct 29, 2026North
9INV-2048Tern Cafe925Oct 30, 2026South

Download this sheet as an Excel practice file (.xlsx)

Sum if not blank with "<>"

SUMIF takes three arguments: the range to test, the criteria, and the range to add. Here column D holds the date each invoice was paid, so "not blank" means "paid". The criteria "<>" is the not-equal operator with nothing after it, which reads as "not equal to empty".

The two ranges must be the same size and start on the same row. D2:D9 with C2:C9 is right. D2:D9 with C3:C10 adds the wrong row's amount without any warning.

=SUMIF(D2:D9,"<>",C2:C9)

Result: 7555

7,555. Six invoices count, including the 760 for Pike & Sons, whose Paid on cell looks empty but holds a space.

Sum if blank with ""

Two double quotes with nothing between them mean "is empty". This is the outstanding total: invoices with no paid date yet.

Add the two results and you get 8,650, the total of every invoice. That proves nothing went missing, but it also shows the problem: Pike & Sons is in the "paid" group, because a cell with a space in it is not empty. The true outstanding total is 1,855, not 1,095.

=SUMIF(D2:D9,"",C2:C9)

Result: 1095

1,095: Mill Lane Bakery (480) and Larkfield School (615). Pike & Sons is missing because of the space in D5.

The fix for a date or number column: ">0"

People clear a cell by pressing the space bar more often than you would think, and data pasted from other systems often carries spaces. When the column should only hold dates or numbers, test for a real value instead of "not empty". Dates are numbers in Excel, so every real date is greater than 0, while a space, a dash or a note like "TBC" is text and is skipped.

This one change is the most reliable "not blank" test for date, amount and quantity columns.

=SUMIF(D2:D9,">0",C2:C9)

Result: 6795

6,795: only the five invoices with a real paid date. The 760 with a space in D5 is no longer counted as paid.

The fix for a text column: ignore spaces

If the test column holds text, such as a name or a reference number, ">0" does not work. Use the wildcard "?*" instead: ? needs at least one character, so it skips truly empty cells. It still counts a cell that holds only a space, though.

To treat spaces as blank too, trim each cell and check its length. SUMPRODUCT handles the whole range at once and works in every Excel version.

=SUMPRODUCT((LEN(TRIM(D2:D9))>0)*C2:C9)

Result: 6795

6,795, the same as ">0": only cells with something other than spaces count. Change >0 to =0 to sum the truly blank rows, which gives 1,855.

When the "blank" cells hold formulas

A cell with a formula such as =IF(B2="","",B2) can show nothing, but Excel does not treat it as empty: ISBLANK says FALSE and COUNTA counts it. Criteria based on "<>" can count those rows as filled too, so a "not blank" total can include rows that look empty on screen.

The same fixes work here. ">0" skips the empty text "" because it is not a number, "?*" skips it because it has no characters, and the SUMPRODUCT test above treats it as blank. If you build the column yourself, consider returning a real value (or nothing at all) instead of "".

Add more conditions with SUMIFS

SUMIFS puts the sum range first, then any number of range and criteria pairs. Each row must pass every test. Here: paid invoices in the North region.

Use the same ">0" fix inside SUMIFS if the column might hold spaces. For unpaid North invoices, swap "<>" for "". Watch the argument order when you switch from SUMIF: in SUMIF the sum range comes last, in SUMIFS it comes first.

=SUMIFS(C2:C9,D2:D9,"<>",E2:E9,"North")

Result: 3890

3,890: Harbor Dental, Ostrander Law and Nova Print. Larkfield School is North but unpaid.

Common mistakes

A row that looks empty is counted as not blank

The cell holds a space or a formula that returns "". Use ">0" for date and number columns, or SUMPRODUCT((LEN(TRIM(range))>0)*sum_range) for text.

Typing <> without quotes

=SUMIF(D2:D9,<>,C2:C9) is not a valid formula. The criteria is text, so the operator goes in quotes: "<>".

Sum range and test range on different rows

D2:D9 must line up with C2:C9. SUMIF does not check that the rows match, so C3:C10 would add each invoice's neighbor without any warning.

Using SUMIF to skip blanks in the column you are adding

SUM already ignores empty cells. "Not blank" only matters when the test is on a different column.

FAQ

How do I sum if a cell is not blank in Excel?

Use =SUMIF(test_range,"<>",sum_range), for example =SUMIF(D2:D9,"<>",C2:C9). It adds the values in C wherever the cell in D is not empty.

How do I sum if a cell is blank?

Use "" as the criteria: =SUMIF(D2:D9,"",C2:C9). It adds the values where the cell in D is empty. Cells that hold a space are not included, so clean those first or use a SUMPRODUCT test with TRIM.

Why does SUMIF "<>" include cells that look empty?

They are not really empty. They hold a space, an apostrophe, or a formula returning "". Use ">0" for date or number columns, or "?*" for text that should have at least one character.

Can I use SUMIFS with "not blank" and another condition?

Yes: =SUMIFS(C2:C9,D2:D9,"<>",E2:E9,"North"). Add as many range and criteria pairs as you need, and every one must be true for the row to be added.

Functions used

Related guides

Updated