SUMIF Formula Generator (2026) — Sum with Conditions | Excel & Sheets

Adds the cells specified by a given condition or criteria.

Generated Formula
=SUMIF(range, criteria)

Learning Resources

Want to master Excel? Check out this Top-Rated Course.

Mastering SUMIF: Conditional Sums in Excel & Google Sheets

The SUMIF function is one of the most practical tools for financial analysis, sales reporting, and budget tracking. It sums values based on a single condition — like "sum all sales where the region is North" or "sum expenses where category is Marketing."

How SUMIF Works

The SUMIF function evaluates each cell in a range against your criteria. If it matches, the corresponding value from the sum_range is added to the total. If you omit sum_range, SUMIF sums the range itself.

Real-World SUMIF Examples

Sum sales by region:

=SUMIF(A:A, "North", B:B)

Sum amounts greater than 500:

=SUMIF(B:B, ">500")

Sum with a cell reference as criteria:

=SUMIF(A:A, E1, B:B)

SUMIF vs SUMIFS: When to Use Each

SUMIF handles one condition. When you need multiple conditions — like "sum sales for Product X in the North region during Q1" — use SUMIFS. The key difference: SUMIFS puts the sum_range first, then pairs of criteria_range and criteria.

Pro Tip: Use wildcards for flexible matching. =SUMIF(A:A, "*Widget*", B:B) sums all rows where column A contains "Widget" anywhere in the text. Use ? for single-character matching.

Common Errors & Fixes

  • SUMIF returns 0 or wrong sum

    Causes:
    • Criteria not in quotes for text (e.g. "Apple" not Apple).
    • Sum_range and range different sizes; only overlapping rows are summed.
    • Number stored as text in range; criteria does not match.
    Fixes:
    • Use quotes for text: ">100", "Sales".
    • Make sum_range same size as range, or omit sum_range to sum range.
    • Align data types; use VALUE or TEXT as needed.

Frequently Asked Questions

What is the difference between SUMIF and SUMIFS?

SUMIF has one condition; SUMIFS can have multiple conditions. Use SUMIFS when you need to sum only when two or more criteria are met.

Can SUMIF use wildcards?

Yes. Use * for any characters and ? for one character. Example: =SUMIF(A:A,"*apple*",B:B) sums B where A contains "apple".

How do I sum with a date criteria?

Use a cell reference or DATE() in criteria, e.g. =SUMIF(A:A,">="&DATE(2025,1,1),B:B) or =SUMIF(A:A,">="&E1,B:B) where E1 has the date.

Why does SUMIF return 0?

Check that criteria match the data type (e.g. number vs text). Use quotes for text: "=100" or ">50". Ensure sum_range aligns with range if you use it.

When should I use SUMIF vs COUNTIF?

Use SUMIF to add values that meet a condition. Use COUNTIF to count how many cells meet a condition. Both use the same criteria syntax.

Related Formulas

SUMIFSCOUNTIFCOUNTIFSAVERAGEIFVLOOKUP

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More