How to Fix SUMIF Returning Zero for Multiple Criteria in Excel
Question details
The user needs to resolve an issue where the SUMIF or SUMIFS formula returns a result of zero when trying to sum values based on multiple text criteria.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Summing a range of numerical values based on multiple distinct text labels located within a single criteria column.
- Observed behavior
- The formula returns zero because placing multiple criteria for the same column in a standard SUMIFS function applies AND logic, making it impossible for a single cell to meet all conditions simultaneously.
Verify that the text criteria in your formula exactly match the spelling in your worksheet, and ensure the values in your sum range are formatted as numbers rather than text.
Use SUM and SUMIFS with an Array Constant
The most effective way to apply OR logic for multiple criteria in the same column is to wrap a SUMIFS formula containing an array constant inside a SUM function.
A standard SUMIFS formula uses AND logic, meaning Excel searches for cells that meet all conditions at the same time. Since one cell cannot contain multiple distinct text values simultaneously, the result is zero.
By placing your criteria in curly brackets (an array constant), Excel calculates the sum for each criterion separately and then adds those totals together using the outer SUM function.
Click on the empty cell where you want the total calculated sum to appear.
Type the formula starting with =SUM(SUMIFS(. For example: =SUM(SUMIFS(F37:F43, A37:A43, {"Beginning Stock Balance", "Customer Replenishment", "Sale/Invoice"})).
Ensure that the sum range (e.g., F37:F43) and the criteria range (e.g., A37:A43) correctly map to your specific dataset.
Press the Enter key on your keyboard to execute the formula and display the correct total.
Convert Numbers Stored as Text to Numeric Values
If the array formula correctly processes the criteria but still returns zero, the values in your sum range may be stored as text, which Excel cannot mathematically sum.
Solve Advanced Formulas Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced array formulas, including nested SUM and SUMIFS calculations. You can effortlessly manage complex datasets and apply OR logic for multiple criteria without worrying about formula syntax compatibility.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file containing the data.
- 2. Input the array formula: Select the target cell and type your =SUM(SUMIFS(Sum_Range, Criteria_Range, {"Criterion1", "Criterion2"})) formula.
- 3. Calculate instantly: Press Enter to instantly process the multiple criteria calculation and view your accurate data sum.
Frequently Asked Questions
Why does a standard SUMIFS function with multiple criteria in the same column return zero?
The SUMIFS function uses AND logic for all specified criteria. If you specify two different text conditions for the exact same column, the formula looks for a single cell that is simultaneously equal to both text strings. Since this is impossible, it finds no matches and returns zero.
Can I use cell references inside the array constant brackets?
No, array constants enclosed in curly brackets {} only accept literal text strings or numbers. They do not accept cell references or formulas. If you need to use cell references for your criteria, you should use the SUMPRODUCT function or an alternative array setup instead.
What does a #SPILL! error mean when checking values with the ISNUMBER formula?
A #SPILL! error occurs in newer spreadsheet versions when an array formula attempts to output multiple results into adjacent cells, but those target cells are not empty or the formula is placed inside a formatted Table. To fix this, test a single cell (e.g., =ISNUMBER(A1)) instead of an entire range.
Can I use this SUM and SUMIFS array method across different columns?
If your criteria are located in entirely different columns, you do not need the array constant method. You can simply use standard SUMIFS arguments, as SUMIFS is naturally designed to apply AND logic across different columns seamlessly.
