How to Make SUMIFS Ignore Blank Criteria Cells in Excel
Question details
The user wants to configure a SUMIFS formula to ignore a specific criteria cell (such as a dropdown menu) if it is left blank, effectively applying no filter for that condition and summing all corresponding values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating dynamic dashboards or reporting templates where a user can select dropdown filters to narrow down a SUMIFS calculation, but needs a blank selection to return all records rather than zero.
- Observed behavior
- By default, referencing a blank criteria cell in a SUMIFS formula forces the function to look specifically for empty cells in the data range, often resulting in an incorrect sum of 0.
Verify that your SUMIFS data ranges and criteria ranges are exactly the same size to avoid value errors before modifying your formula.
Use an IF Function with an Asterisk Wildcard
You can wrap your criteria reference in an IF function to return an asterisk wildcard (*) when the cell is empty. This tells SUMIFS to include all text values.
In Excel, the asterisk (*) is a wildcard that represents any sequence of text characters. By combining this with an IF statement, you can instruct the formula to use the specific criteria when a user types it, or use the wildcard to sum everything when the user leaves the criteria cell blank.
Click on the cell where you want your dynamic SUMIFS result to appear.
Begin your formula by defining your sum range and criteria range. For example: =SUMIFS(E:E, F:F, ...
Instead of selecting the criteria cell directly (e.g., C3), enter the IF statement: IF(C3="","*",C3). This translates to: 'If C3 is empty, use the wildcard, otherwise use the value in C3'.
Your complete formula will look similar to: =SUMIFS(E:E, F:F, IF(C3="","*",C3)). Press Enter to calculate. When C3 is cleared, the formula will now sum all rows.
Master Complex Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced functions like SUMIFS, IF statements, and wildcards. Handle complex data analysis and dynamic dashboards effortlessly with a free, professional tool.
- 1. Open your data file: Launch WPS Spreadsheet and open your existing data tracking file.
- 2. Enter your SUMIFS formula: Select the target cell, type =SUMIFS(), and select your ranges exactly as you would in other spreadsheet software.
- 3. Apply dynamic criteria: Use the IF(C3="","*",C3) logic to seamlessly create dynamic dashboards without compatibility issues.
Frequently Asked Questions
Why does my SUMIFS return 0 when the criteria cell is blank?
By default, if you reference an empty cell as a criteria, SUMIFS interprets this as a literal search for empty cells in your criteria range. If there are no blank cells matching your other conditions, the sum returned will be 0.
Does the wildcard method work with numeric data?
No, the asterisk (*) wildcard only matches text strings. If the column you are filtering contains numbers, the wildcard method will fail. You might need to convert the numbers to text or use a different formula logic, such as dynamically changing the entire formula based on the cell's state.
Can I use this same IF technique with COUNTIFS or AVERAGEIFS?
Yes. The exact same logic of combining the IF function with an asterisk wildcard works seamlessly for COUNTIFS, AVERAGEIFS, and similar conditional functions in Excel.
