To sum values where the matching cell is blank, use empty double quotes as the criteria: =SUMIF(A2:A13,"",B2:B13) adds up column B for every row where column A is empty. That’s the whole technique — the “” tells Excel to look for cells with nothing in them.
Two things behave differently from what people expect. A cell containing only a space isn’t blank, so it’s skipped; to catch both, use =SUM(SUMIFS(B2:B13,A2:A13,{""," "})), or =SUMPRODUCT(--(LEN(TRIM(A2:A13))=0),B2:B13), which treats anything that trims to nothing as blank.
And a cell holding a formula that returns “” looks empty and is matched by this criteria, even though ISBLANK would report it as not blank. For the opposite case — summing where the cell has something in it — use "<>" as the criteria. To add a second condition alongside the blank test, switch to SUMIFS: =SUMIFS(C2:C13,A2:A13,"",B2:B13,"North").
Let’s say you are coordinating a fundraising campaign for a non-profit organization. You have an Excel worksheet where column A lists donor names, column B shows the donation amounts, and some names in column A are left blank – perhaps because some donors chose to remain anonymous or their details were not captured during the donation process.
So here, we need to sum the values for the cells which are blank to get the amount from the anonymous donors.
Now, to use SUMIF to sum values for the blank criteria, you just need to specify a blank value in the criteria argument in the SUMIF function. With this, you tell Excel to only consider cells where there’s no value. In the example below, we have three blank cells in the name column for which we need to sum the donation amount.

In this tutorial, we will learn to use the SUMIF or SUMIFS function to sum for blank values.
Write a Formula to Use SUMIF for Blank Values
As I have mentioned, for this, you can use the SUMIF function to sum the values in column B.
- First, enter SUMIF in a cell where you want to calculate the sum.
- Now, refer to the Name column where you have blank cells.
- After that, enter double quotation marks (starting and closing).
- Next, refer to the Donation column from where you need to sum the values.
- In the end, hit Enter to get the result.

=SUMIF(A2:A13,"",B2:B13)
- Name Range (A2:A13): This is the list of donor names. The formula looks here to find blank cells where names should be.
- Criteria (“”): The criteria is set to find the blank cells. When it finds a cell without a name, it means the donation was made anonymously.
- Sum Range (B2:B13): The donation amounts are listed here. The formula adds up the donations that match the blank name criteria from the range above.
Note that the “” criteria also matches cells with a formula that returns an empty string (“”), as they look blank too. And to sum the donations where the name is not blank, use “<>” as the criteria:
=SUMIF(A2:A13,"<>",B2:B13)
Create a Dynamic Formula for SUMIF Blank
To make a formula dynamic, enabling it to automatically adjust as data is added or removed from your dataset, you can use the OFFSET function along with COUNTA in SUMIF.
=SUMIF(OFFSET(A1, 1, 0, COUNTA(B:B) - 1, 1), "", OFFSET(B1, 1, 0, COUNTA(B:B) - 1, 1))
This formula uses COUNTA(B:B) – 1 to count all the non-empty cells in column B (the amounts), subtracting one to exclude the header. This gives us the number of actual data entries. Then, OFFSET(A1, 1, 0, …) is used to start the range from the second cell (A2) and extend it to cover the number of entries we counted.
The same count is used for the range in column B, so both ranges have the same size. The count must come from column B, not column A, because column A has blank names by design, and COUNTA would skip them and make the range too short. This also means column B shouldn’t have blank cells in between. This way, the formula dynamically adjusts to include just the right amount of data as it changes, like when new values are added or existing ones are deleted.
A Problem You Might Face
There might be a situation where you have one or more cells with a space as the value, which look like blank cells but aren’t. In that case, SUMIF will ignore that cell and won’t include it while calculating the sum.

Here, you can create a formula with the help of SUMIFS and SUM to create a SUMIF with OR logic.

In this formula, you need to specify two criteria in the criteria1 argument using curly brackets.
- Space
- Blank

When you do this, SUMIFS returns one sum for each criterion, and SUM adds them up. You don’t need to enter it as an array formula: a formula with an array constant like this works with a normal Enter in all versions.
=SUM(SUMIFS(B2:B13,A2:A13,{“”,” “}))
And you can also use SUMPRODUCT for this:
=SUMPRODUCT(--(LEN(TRIM(A2:A13))=0),B2:B13)
- TRIM(A2:A13): This function removes the extra spaces from the donor names in the range A2 to A13, so a cell with only spaces becomes empty. It must use only the names column: with A2:B13, the ranges in SUMPRODUCT have different sizes, and the formula returns #VALUE!.
- LEN(TRIM(A2:A13))=0: This part of the formula simply calculates the length of the trimmed content of each cell in the specified range. If a donor’s name cell is blank, even after trimming, its length will be 0.
- –(LEN(TRIM(A2:A13))=0): The double negation (–) converts the Boolean values TRUE and FALSE into 1s and 0s, respectively. This results in an array where each element is 1 if the corresponding donor name is blank (after trimming) and 0 otherwise.
- B2:B13: This range contains the donation amounts corresponding to each donor name listed in A2 to A13.
- SUMPRODUCT(–(LEN(TRIM(A2:A13))=0),B2:B13): SUMPRODUCT multiplies arrays together and then sums the result. This formula multiplies the array of 1s and 0s generated from donor name conditions by the corresponding donation amounts. Only the donations where the donor name is blank are summed because multiplying by 0 nullifies the other values.
Other Examples of Using SUMIF for Blank Values
Combine SUM with ISBLANK Inside an Array Formula
This is an array formula that sums the values in B2:B13 where the cells in A2:A13 are blank. In Excel 2019 and older, enter it with Ctrl + Shift + Enter. Unlike SUMIF with “”, ISBLANK is TRUE only for truly empty cells, so a formula that returns “” isn’t counted.
=SUM(ISBLANK(A2:A13) * B2:B13)
Sum Blank Values with One More Condition
Use SUMIFS to add a second condition. The following formula sums the donations over 100 where the donor name is blank:
=SUMIFS(B2:B13, A2:A13, "", B2:B13, ">100")
Sum Alternate Values for Blanks
If you want to count each blank amount as a fixed value (like 50) in the total, add the sum of the amounts to the number of blank cells multiplied by that value:
=SUM(B2:B13) + COUNTBLANK(B2:B13) * 50
Sum a Constant Number for Each Blank Cell in a Range
SUMIF needs a range in the sum_range argument, so a number like 1 doesn’t work there. Count the blank cells with COUNTBLANK and multiply by the constant instead (5 in this example):
=COUNTBLANK(A1:A13) * 5
Sum with an Indirect Reference to Adjacent Cells
=SUMIF(A1:A10, "", OFFSET(A1:A10, 0, 1))
Here, OFFSET(A1:A10, 0, 1) refers to the range one column to the right of A1:A10 (that’s B1:B10), so the formula sums the values in column B where the cell in column A is blank.
Frequently Asked Questions
Why does SUMIF with “” not include some blank-looking cells?
Those cells have a space or another invisible character in them. Use the SUMPRODUCT formula with TRIM shown above, or clean the data with Find and Replace first.
How do I count blank cells instead of summing?
Use COUNTBLANK, like the formula below. It counts empty cells and cells with a formula that returns “”.
=COUNTBLANK(A2:A13)
Can I use ISBLANK as the criteria in SUMIF?
No. SUMIF takes a criteria value like “” or “<>”, not a function. To use ISBLANK, use the SUM(ISBLANK(…)*…) array formula or SUMPRODUCT instead.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.