Formula Errors

How to Fix Excel SUMIFS Formula Returning Zero or an Error

Elise Williams Oct 9, 2026 869 views

Question details

The SUMIFS formula calculates to zero or displays an error code when referencing data and criteria across different worksheets.

How to Fix Excel SUMIFS Formula Returning Zero or an Error

Product: Excel
Device & OS: not provided
Scenario: Attempting to sum values based on specific text conditions using data pulled from another worksheet.
Observed behavior: The formula returns 0 or a calculation error instead of the correct mathematical sum, usually due to formatting mistakes like extra equals signs before text.

Before you start

Verify that your sum range and all criteria ranges contain the exact same number of rows and columns, as mismatched range sizes are the leading cause of SUMIFS calculation errors.

Solution 1Recommended

Remove Unnecessary Equals Signs from Text Criteria

Fix syntax errors by typing text criteria directly inside quotation marks without an equals sign.

When hardcoding text conditions into a SUMIFS formula, placing an equals sign inside the quotes (e.g., "=August") can confuse the formula parser and result in a zero sum.

Select the formula cell

Click on the cell displaying the incorrect zero or error value to reveal the current formula in the formula bar at the top of the worksheet.

Check the criteria formatting

Review your text conditions in the formula. If any text criteria include an equals sign before the word, delete it so that only the word remains inside the quotes.

Apply the corrected formula

Update the formula to match the correct structure, for example: =SUMIFS('Total FY23-24'!D2:D82, 'Total FY23-24'!A2:A82, "August", 'Total FY23-24'!C2:C82, "D"), and press Enter.

Remove Unnecessary Equals Signs from Text Criteria

Text Criteria Rule: Exact match text criteria do not require a logical operator. Just enclose the exact text string in double quotes.

Solution 2

Use Absolute Cell References for Dynamic Criteria

Reference specific cells containing your criteria instead of typing text manually, allowing you to easily copy the formula down a column.

Easily Build Error-Free Formulas with WPS Spreadsheet

WPS Spreadsheet provides an intelligent formula builder and built-in error checking to help you correctly set up complex functions like SUMIFS across multiple worksheets without syntax struggles.

1. Open your workbook: Launch WPS Spreadsheet and open the file where you need to calculate conditional sums.
2. Use the Insert Function tool: Navigate to the Formula tab on the ribbon and click 'Insert Function', then search for and select the SUMIFS function.
3. Fill in the dialog boxes: Follow the prompts in the function arguments dialog to select your sum range and criteria ranges. The tool automatically adds quotation marks and formats the syntax correctly for you.

Fully compatible with Microsoft Excel formulas and functionsIntelligent formula prompts and syntax hints to prevent typosInteractive 'Insert Function' dialog to guide you step-by-stepLightweight software with a familiar, easy-to-use interface

microsoft office alternative - wps office

Frequently Asked Questions

Why is my SUMIFS formula returning a #VALUE! error?

A #VALUE! error in SUMIFS almost always occurs because the sum_range and the criteria_range are different sizes. Check your formula to ensure both ranges cover the exact same number of rows and columns.

How do I use greater than or less than operators in SUMIFS?

When hardcoding a logical operator with a number, enclose both in double quotes (e.g., ">50"). If you want to use an operator with a cell reference, enclose the operator in quotes and use an ampersand to join the cell (e.g., ">"&A2).

Can I use wildcards to find partial text matches in SUMIFS?

Yes, you can use an asterisk (*) to match a sequence of characters or a question mark (?) to match a single character. For example, using "*August*" as your criteria will sum rows where the cell contains the word August anywhere in the text string.

Excel & Data Problems