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, 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 SUMIF or SUMIFS function to sum for blank values.
Write a formula to use SUMIF for Blank Values
- 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)
A Problem You Might Face
There might be a situation where you have a range of cells where you have a cell or multiple cells with space as a value in the cell which looks like a blank cell, but not a blank cell.
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 OR.
In this formula, you need to specify two criteria in the criteria1 argument using curly brackets.
- Space
- Blank
When you do this, SUMIF considers both criteria simultaneously. And to get the result, you need to enter it as an array formula (use Ctrl + Alt + Enter).
=SUM(SUMIFS(B2:B13,A2:A13,{“”,” “}))
You can learn more about it here.
Get the Excel File
DownloadMore
- Sum Greater than Values using SUMIF
- Sum Not Equal Values (SUMIFS) in Excel
- SUMIF / SUMIFS with an OR Logic in Excel
- SUMIF with Wildcard Characters in Excel
- SUMIFS Date Range (Sum Values Between Two Dates Array)
- Combine VLOOKUP with SUMIF
- Sum IF Cell Contains a Specific Text (SUMIF Partial Text)
- Sum Values Based on Year (SUMIF Year)
- SUMIF By Date (Sum Values Based on a Date)
- SUMIF Non-Blank (Sum Values for Non-Empty Cells)
- Sum Values Less Than a Particular Value (SUMIF Less Than)
- Sum Values that are Greater Than Zero (SUMIF)
- Sum Values Based on the Month (SUMIF)
⇠ Back to Top Excel Formulas List