Fix Excel SUMIFS When Formula Results That Look Blank Are Included
Question details
Exclude cells whose formulas return an empty string from an Excel SUMIFS total.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user is summing values with multiple conditions and wants column H to be included only when its displayed result is not blank.
- Observed behavior
- ISBLANK returns FALSE for formula cells that display an empty string, and the SUMIFS "<>" criterion treats those cells as nonblank.
Check whether the relevant cells contain formulas that return "" rather than truly empty cells. Also confirm whether the criteria column can contain numeric values, because text-only criteria do not include numbers.
Use the ?* criterion to include only non-empty text results in SUMIFS
Use this method when the cells in the criteria column contain text and formulas returning empty strings should be excluded.
A cell containing a formula is not truly blank, even when its displayed result is an empty string. The ?* criterion matches text with at least one character, so it excludes formula results of "".
In the SUMIFS formula, replace the criterion for column H such as "<>" with "?*".
Enter =IFERROR(SUMIFS('Cleaned Data'!F:F,'Cleaned Data'!D:D,"75 Minute All-Inclusive Massage",'Cleaned Data'!E:E,"Cedtara",'Cleaned Data'!H:H,"?*"),"$0.00") in the result cell.
Press Enter and verify that rows where column H displays an empty string are no longer included in the sum.
Use SUMPRODUCT when the criteria column may contain text or numbers
Use this method when column H can contain either numbers or text and formula-generated empty strings must still be excluded.
Exclude Formula-Generated Empty Strings in WPS Spreadsheets
WPS Spreadsheets supports SUMIFS, SUMPRODUCT, IFERROR, and wildcard criteria, so you can adjust a nonblank test to exclude formula results that display as empty strings while preserving your workbook logic.
- 1. Open the workbook in WPS Spreadsheets: Launch WPS Office, select Spreadsheets, and open the Excel workbook containing the SUMIFS formula.
- 2. Select the total formula cell: Click the cell with the SUMIFS result, then use the formula bar to edit its criteria.
- 3. Apply a displayed-value test: Replace the nonblank criterion with "?*" for text-only values, or enter a bounded-range SUMPRODUCT formula when the column can contain numbers.
- 4. Recalculate and verify the result: Press Enter, then compare the total against rows whose criteria cells visibly contain values.
Frequently Asked Questions
Why does ISBLANK return FALSE when a cell looks empty?
ISBLANK returns FALSE when the cell contains a formula, including a formula such as =IF(A2="","",A2) that returns an empty string. The cell appears blank but is not an empty cell.
Why does the SUMIFS "<>" criterion include formula-generated empty strings?
The "<>" criterion checks whether the cell is not truly blank. Because a formula is present, Excel treats a formula cell as nonblank even when its result displays as "".
What does ?* mean in a SUMIFS criterion?
The question mark matches one character and the asterisk matches additional characters. Together, "?*" matches text containing at least one character and excludes an empty string.
Will ?* include numbers in the criteria range?
No. The ?* wildcard criterion is intended for text. If the criteria range may contain numbers as well as text, use SUMPRODUCT with a condition such as H2:H1000<>"".
Can I make formula cells truly blank instead of returning an empty string?
A formula cell cannot become truly empty while the formula remains in the cell. You can change the source data process to leave cells empty, or use criteria that explicitly exclude formula results of "".
