Formula Errors

How to Fix SUMIFS Returning Zero in Excel and WPS Spreadsheet

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to diagnose and fix a spreadsheet issue where the SUMIFS function unexpectedly returns a value of zero despite having data that appears to meet the specified criteria.

Product: Spreadsheet
Device & OS: not provided
Scenario: Calculating conditional sums across multiple criteria using the SUMIFS formula.
Observed behavior: The formula outputs a 0 instead of the expected calculated sum, typically caused by formatting mismatches, syntax errors, or unseen characters.

Before you start

Ensure your workbook calculation mode is set to 'Automatic' rather than 'Manual' in the Formulas tab, and unhide any rows or columns to get a complete view of your data range.

Solution 1Recommended

Convert Text-Formatted Numbers to Numeric Values

Numbers stored as text will be ignored by the SUMIFS function, resulting in a zero sum. Converting them to actual numbers resolves this.

One of the most common reasons SUMIFS returns zero is that the numbers in the sum range are formatted as text. This often happens when data is exported from external databases or ERP systems.

Spreadsheet applications cannot perform mathematical addition on text strings.

Select the sum range

Highlight the entire column or specific cells containing the values you are trying to add together.

Check for the error indicator

Look for a small green triangle in the corner of the cells. If present, click the warning icon that appears next to the selection.

Convert to number

Select 'Convert to Number' from the drop-down menu. Alternatively, go to the Data tab, click 'Text to Columns', and simply click 'Finish' to quickly convert the column formatting.

Quick Formatting Tip: You can also multiply the entire column by 1 using 'Paste Special -> Multiply' to instantly force text into numeric formatting.

Solution 2

Remove Hidden Spaces from Data and Criteria

Extra spaces in your criteria or data range prevent the SUMIFS function from finding an exact match, causing it to return zero.

Solution 3

Verify Formula Syntax and Align Range Sizes

SUMIFS requires all specified ranges to be exactly the same size. Mismatched ranges will disrupt the calculation.

Seamless Spreadsheet Calculations

Calculate Conditional Sums Accurately with WPS Spreadsheet

Easily construct and troubleshoot SUMIFS and other complex formulas using the intuitive built-in function builder in WPS Spreadsheet, fully compatible with Microsoft Excel.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
2. Launch the function builder: Select the cell where you want the sum to appear, click on the 'Formulas' tab, and select 'Insert Function'.
3. Input parameters interactively: Search for SUMIFS. Use the dialog box to cleanly select your Sum_range and Criteria_ranges without typing complex syntax manually.
4. Calculate and verify: Press Enter or click OK. The built-in error checking will alert you if ranges are mismatched or data types are inconsistent.

100% compatible with Microsoft Excel formulas and .xlsx file formatsBuilt-in error checking to instantly highlight text-formatted numbersIntuitive Insert Function dialog for building error-free criteria rangesFree to use with a lightweight installation and familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I use wildcards with SUMIFS to fix matching errors?

Yes, you can use wildcards in your criteria to match partial text. Use an asterisk (*) to match a sequence of multiple characters, or a question mark (?) to match a single character. For example, "*apple*" will sum all cells containing the word apple.

Why does my SUMIFS formula show #VALUE! instead of zero?

A #VALUE! error in a SUMIFS calculation typically indicates that the criteria range and the sum range do not have the same number of rows and columns. Ensure all ranges in the formula are identical in size.

Do dates need special formatting in SUMIFS criteria?

Yes, when typing a date directly into the formula criteria, it should be enclosed in quotation marks, such as ">1/1/2023". Alternatively, you can refer to a specific cell containing a valid date serial number without using quotation marks.

Excel & Data Problems