How to Use SUMIFS to Match Blank Criteria Cells in Excel
Question details
The user needs to calculate a sum using the SUMIFS function where one of the required criteria is matching empty or blank cells.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Applying multiple conditions in a SUMIFS formula, specifically needing to include rows where a designated criteria column contains no data.
- Observed behavior
- The formula fails to correctly sum the target rows if the blank condition is supplied incorrectly, as Excel requires a specific syntax to recognize blank criteria.
Ensure your dataset does not contain hidden characters like spaces in the 'blank' cells, as Excel treats these differently than truly empty cells.
Use the "=" Operator Directly in the SUMIFS Formula
The most direct and reliable way to match completely empty cells in a SUMIFS function is to type "=" as the criteria argument.
Excel recognizes the equals sign inside quotation marks as a command to look for absolute blank cells. This method doesn't require helper cells and keeps your formula self-contained.
Click on the cell where you want the final sum to be displayed.
Start typing your formula. Enter the sum range and your first criteria range. When it comes to the criteria argument for blank cells, type "=" (including the quotation marks).
Your formula should look similar to =SUMIFS(B2:B10, A2:A10, "="). Press Enter to calculate the sum.
Reference an Empty Cell with the "=" Operator
You can combine the equals operator with a cell reference pointing to a known empty cell or a cell containing a blank string formula.
Use SUMIFS Seamlessly in WPS Spreadsheet
WPS Spreadsheet provides complete support for advanced data functions like SUMIFS, allowing you to match blank criteria cells exactly as you would in Microsoft Excel. Enjoy a familiar interface and comprehensive data analysis tools entirely for free.
- 1. Open your dataset: Launch WPS Spreadsheet and open your .xlsx workbook containing the data you want to sum.
- 2. Input the SUMIFS function: Select the target cell, type =SUMIFS(, and select your sum range followed by your criteria range.
- 3. Apply the blank criteria: Type "=" as your criteria argument to match blank cells, close the parenthesis, and press Enter.
Frequently Asked Questions
Why does SUMIFS return 0 when I leave the criteria argument empty?
If you leave the criteria argument entirely blank (e.g., placing nothing between commas), Excel may not interpret it as a command to find empty cells. You must explicitly define the blank criterion using "=" or "".
How do I match cells that are not blank using SUMIFS?
To sum rows where the criteria cells contain data (are not empty), use the not-equal-to operator "<>" as your criterion in the SUMIFS formula.
What is the difference between "=" and "" in SUMIFS criteria?
The "=" criteria strictly matches completely empty (blank) cells. Using "" (an empty string) can match both empty cells and cells that contain a formula currently returning a blank string.
Can I use COUNTIFS to count blank cells with the same logic?
Yes, the exact same criteria logic applies to the COUNTIFS function. You can use "=" as the criteria argument to reliably count rows that have blank cells.
