Google Sheets SUMIFS Not Working? Here’s What To Do

Your SUMIFS formula in Google Sheets is returning 0, incorrect totals, or no result at all, even though matching data exists.
This usually shows up when criteria don’t match properly, ranges are misaligned, or data types are inconsistent.

This guide fixes it step by step.

Why the Issue Happens

• Sum range and criteria ranges are different sizes
• Text vs number mismatch (e.g., 100 vs "100")
• Extra spaces or hidden characters
• Incorrect criteria syntax (missing quotes, wrong operators)
• Dates not recognized properly
• Using full-column references inconsistently
• Case or formatting inconsistencies
• Blank or null values interfering

Step-by-Step Fixes

Step 1: Use Correct SUMIFS Structure

Basic syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2])

Example:

=SUMIFS(C2:C100, A2:A100, "Sales", B2:B100, ">100")

• C2:C100 → values to sum
• A2:A100 → first condition
• B2:B100 → second condition

Step 2: Match Range Sizes Exactly

All ranges must be equal.

Wrong:

=SUMIFS(C2:C100, A2:A50, "Sales")

Correct:

=SUMIFS(C2:C100, A2:A100, "Sales")

Step 3: Fix Text vs Number Mismatch

Example:

• Criteria = 100
• Data = "100"

Fix:

=SUMIFS(C2:C100, A2:A100, VALUE(E1))

Or standardize data format.

Step 4: Remove Extra Spaces

Hidden spaces prevent matches.

Fix:

=SUMIFS(C2:C100, ARRAYFORMULA(TRIM(A2:A100)), "Sales")

Or clean data beforehand:

=ARRAYFORMULA(TRIM(A2:A100))

Step 5: Use Correct Criteria Syntax

Text must be in quotes:

=SUMIFS(C2:C100, A2:A100, "Sales")

Numbers with conditions:

=SUMIFS(C2:C100, B2:B100, ">100")

Wrong:

=SUMIFS(C2:C100, B2:B100, >100)

Step 6: Fix Date Criteria

Dates must be properly formatted.

Wrong:

=SUMIFS(C2:C100, B2:B100, ">01/01/2024")

Correct:

=SUMIFS(C2:C100, B2:B100, ">"&DATE(2024,1,1))

Step 7: Check for Hidden Characters

Imported data may contain invisible characters.

Fix:

=SUMIFS(C2:C100, ARRAYFORMULA(TRIM(CLEAN(A2:A100))), "Sales")

Step 8: Debug Using COUNTIFS

Check if criteria matches exist:

=COUNTIFS(A2:A100, "Sales")

If result = 0 → no match (data issue).

Step 9: Avoid Full Column References

Using:

=SUMIFS(C:C, A:A, "Sales")

can slow or break logic in large sheets.

Better:

=SUMIFS(C2:C1000, A2:A1000, "Sales")

Step 10: Handle Blank or Missing Values

Blanks may affect results.

Fix:

=SUMIFS(C2:C100, A2:A100, "<>")

This excludes blanks.

Common Mistakes

• Mismatched range sizes
• Missing quotes in criteria
• Ignoring text vs number mismatch
• Not cleaning spaces or hidden characters
• Using incorrect date format
• Expecting results when no match exists
• Using inconsistent references

Pro Tips / Better Alternatives

Use SUM + FILTER (More Flexible)

=SUM(FILTER(C2:C100, A2:A100="Sales", B2:B100>100))

Easier to debug and more flexible.

Use QUERY for Aggregation

=QUERY(A1:C100, "SELECT SUM(C) WHERE A='Sales' AND B>100", 1)

Efficient for large datasets.

Use Helper Columns for Clean Data

=TRIM(CLEAN(A2))

Then apply SUMIFS on cleaned data.

Use Named Ranges

Instead of:

A2:A100

Use:

SalesData

Improves readability.

Validate Inputs Before Calculation

Ensure:

• No blanks
• Correct formats
• Clean data

Bottom Line

If SUMIFS isn’t working, fix in this order:

1. Match all range sizes
2. Use correct criteria syntax
3. Fix data types (text vs number)
4. Clean spaces and hidden characters
5. Handle date formatting properly
6. Verify matches using COUNTIFS

Most issues come from data inconsistencies and syntax errors.
Fix those, and SUMIFS will work accurately.