Fix SUMIFS Returning Zero with Multiple Criteria in Excel
Question details
A SUMIFS formula works perfectly with a single condition but fails and returns 0 when an array or multiple cell range is used as the criterion for one of the arguments.
- Product
- Microsoft Excel
- Device & OS
- Mac OS (Excel 2016)
- Scenario
- Attempting to sum data based on multiple criteria (such as month and week) while matching the results against a multi-cell range containing several specific agent names.
- Observed behavior
- The formula returns a zero when the multi-cell range is referenced for the agent criterion, instead of correctly calculating the sum of the matching values.
Ensure that your criteria ranges and source data share the exact same formatting (e.g., all formatted as text) and do not contain hidden leading or trailing spaces, as this will also cause SUMIFS to fail.
Wrap SUMIFS Inside a SUM Function using an Array
Use this method to force Excel to evaluate the SUMIFS logic for each item in your multi-criteria array and sum the final totals together.
By default, the SUMIFS function relies on AND logic between its criteria. When you supply a range of multiple values for a single criterion (like a list of agent names), Excel cannot natively process it as an OR logic condition, which causes it to return zero.
To bypass this limitation, you can nest your SUMIFS function inside a SUM function and enter it as an array formula. This prompts Excel to calculate the SUMIFS for each agent independently and then combine the subtotals.
Click on the cell where you want the final calculated total to appear.
Type the formula structure: =SUM(SUMIFS(sum_range, criteria_range1, criteria1, agent_range, {"Agent 1","Agent 2"})). If you are referencing a cell range for the agents, use that range instead of the bracketed text.
If you are using an older version of Excel (like Excel 2016 for Mac), you must press Command + Return (on Mac) or Ctrl + Shift + Enter (on Windows) to confirm the formula. This correctly registers it as an array formula, wrapping it in curly braces {}.
Use the SUMPRODUCT Function as an Alternative
SUMPRODUCT natively handles arrays without requiring special keystrokes in older versions of Excel, making it highly reliable for complex OR conditions.
Handle Complex Multiple Criteria Formulas Seamlessly in WPS Spreadsheet
WPS Spreadsheet fully supports complex array formulas, nested SUMIFS, and SUMPRODUCT functions out of the box. Whether you are dealing with text formats, multiple criteria arrays, or massive datasets, WPS Office ensures accurate calculation without frustrating zero-value errors.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the multi-criteria data.
- 2. Select your calculation cell: Click on the blank cell where you intend to sum the data.
- 3. Input the array formula: Type =SUM(SUMIFS(sum_range, condition_range, {"Criteria 1","Criteria 2"})) directly into the formula bar.
- 4. View instant results: Press Enter to execute. WPS Spreadsheet efficiently processes the array and outputs the precise total instantly.
Frequently Asked Questions
Why does my SUMIFS formula return zero when the criteria are clearly correct?
This usually happens due to data type mismatches (such as numbers stored as text vs. actual numbers), hidden spaces in your cell ranges, or attempting to use an OR condition across a single criteria range without structuring it as an array formula.
How do I apply an OR condition correctly in a SUMIFS formula?
Because SUMIFS natively uses AND logic, you must use an array constant (e.g., {"Value 1","Value 2"}) for the criteria argument and wrap the entire SUMIFS function inside a SUM function. This forces Excel to calculate the sum for each individual condition and add them together.
Does cell formatting affect SUMIFS calculation results?
Yes. SUMIFS requires exact matches. If the criteria in your formula is formatted as a number, but the matching data in your referenced range is formatted as text, the function will fail to recognize the match and will return a zero.
