Formula Errors

How to Fix SUMIFS Returning Zero with Three Criteria in Excel

Maira Mehtab Sep 27, 2026 870 views

Question details

The user is experiencing an issue where the SUMIF or SUMIFS function returns zero or a blank result when filtering by three criteria (e.g., date, customer, and a specific keyword like 'deposit'), despite combinations of two criteria working perfectly.

Product: Excel
Device & OS: not provided
Scenario: Calculating conditional sums using the SUMIFS function across multiple specific conditions.
Observed behavior: The formula evaluates to zero or blank instead of the expected sum, indicating that the criteria combination is failing to find a matching row.

Before you start

Before modifying your formula, ensure that your calculation options are set to Automatic in the Formulas tab, and verify that the sizes of your sum_range and all criteria_ranges are identical.

Solution 1Recommended

Verify Simultaneous Criteria Matching Using AutoFilter

Even if pairs of criteria work independently, a zero result usually means no single row meets all three conditions simultaneously. AutoFilter provides a visual way to confirm row-level matches.

SUMIFS applies an 'AND' logic across all given criteria. If a single row does not contain the exact Date, Customer, AND Deposit designation in their respective columns, it will not be summed.

Enable Filter

Highlight your entire data table, navigate to the 'Data' tab on the ribbon, and click 'Filter'.

Filter the First Criterion

Click the dropdown arrow on your Customer column and select the specific customer name used in your formula.

Filter the Second Criterion

Click the dropdown arrow on the Date column and select the specific date or date range.

Filter the Third Criterion

Click the dropdown arrow on your transaction type column and select 'deposit'. If all rows disappear, your data does not contain a record meeting all three criteria at once.

Criteria Order: Rearranging the criteria inside the SUMIFS function will not change the result; the formula strictly requires all conditions to be met in the same row regardless of order.

Solution 2

Clean Data Formatting and Remove Hidden Spaces

Inconsistent data types, such as numbers stored as text or trailing spaces, cause exact matches to fail in SUMIFS.

Solution 3

Use Helper Columns to Isolate the Failing Criterion

Breaking down the SUMIFS logic into individual helper columns allows you to see exactly which condition is causing the formula to fail.

Efficient Spreadsheet Alternative

Calculate Multi-Criteria Sums Flawlessly with WPS Spreadsheet

WPS Office provides robust support for advanced statistical formulas like SUMIFS, ensuring accurate data analysis and full compatibility with Excel files. Its built-in data cleaning tools make troubleshooting mismatches effortless.

1. Open Your Data File: Launch WPS Spreadsheet and open your existing workbook.
2. Enter the Formula: Select the cell for your total and type =SUMIFS(. The syntax helper will immediately prompt you with the required arguments.
3. Select Ranges and Criteria: First, select the range of values to sum. Then, sequentially select your criteria ranges and type your criteria (e.g., date, customer, "deposit").
4. Calculate: Press Enter. WPS Spreadsheet will calculate the sum matching all specified conditions seamlessly.

Fully compatible with Microsoft Excel formats (.xlsx and .xls)Supports all advanced statistical and math functions including SUMIFSBuilt-in Data Validation and text-to-columns tools for easy data cleaningLightweight and fast, even when calculating large datasets

microsoft office alternative - wps office

Frequently Asked Questions

Why does SUMIF work but SUMIFS returns zero?

SUMIF only evaluates a single condition. When you switch to SUMIFS for multiple criteria, Excel applies 'AND' logic, meaning every single condition must be met simultaneously in the same row. If even one criterion fails due to non-overlapping data, the result is zero.

Can data formatting issues cause SUMIFS to fail?

Yes. If your dates are stored as text, or if there are invisible trailing spaces in your customer names (common in exported reports), the exact match requirement of SUMIFS will fail, resulting in a zero.

Does the order of criteria matter in the SUMIFS function?

The order of the criteria pairs does not affect the calculation as long as each 'criteria_range' is immediately followed by its corresponding 'criterion'. However, the 'sum_range' must always be the very first argument, unlike the standard SUMIF function.

How do I fix trailing spaces in my criteria ranges?

Use the TRIM function. Create a new column with the formula =TRIM(A2) to remove extra spaces from your text data, copy the newly cleaned results, and paste them as 'Values' over the original column.

Excel & Data Problems