How to Use SUMIF in Excel (Sum with One Condition + Free Generator)

How to Use SUMIF in Excel and Google Sheets

The SUMIF function sums numeric values that meet a single condition — like "sum all sales where the region is North" or "sum expenses over $500." It is one of the most practical tools for financial analysis, sales reporting, and budget tracking.

SUMIF Syntax

=SUMIF(range, criteria, [sum_range])

• range — The cells you want to evaluate against the criteria.
• criteria — The condition that determines which cells to sum (text, number, expression, or cell reference).
• sum_range — The actual cells to sum. If omitted, SUMIF sums the range itself.

Step-by-Step Example: Sum Sales by Region

Suppose you have a sales table:

Goal: Total sales for the North region.

1. Click cell D2 (or any empty cell).
2. Enter: =SUMIF(A2:A6, "North", B2:B6)
3. Press Enter. The result is 4,450 (1,200 + 2,300 + 950).

To make the criteria dynamic, put "North" in cell E1 and use: =SUMIF(A2:A6, E1, B2:B6). Update E1 to "South" and the result instantly changes to 850.

More Real-World SUMIF Examples

Sum amounts greater than 500:

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

Sum with a cell reference as criteria:

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

Sum excluding a specific item:

=SUMIF(A:A, "<>Widget", B:B)

Sum with comparison operator + cell reference:

=SUMIF(C:C, ">="&D1, B:B)

Using Wildcards with SUMIF

Wildcards let you match partial text:

• * (asterisk) — matches any sequence of characters. =SUMIF(A:A, "*East*", B:B) sums all rows where column A contains "East" anywhere (includes "Northeast", "Eastern").
• ? (question mark) — matches any single character. =SUMIF(A:A, "??-100", B:B) matches "AB-100" but not "ABC-100".
• ~ (tilde) — escape wildcards. =SUMIF(A:A, "~*", B:B) sums rows where column A contains a literal asterisk.

Summing with Date Criteria

SUMIF handles date conditions using DATE() or cell references:

=SUMIF(A:A, ">="&DATE(2025,1,1), B:B)

This sums all values in column B where the date in column A is on or after January 1, 2025. For a two-date range (between start and end), use SUMIFS:

=SUMIFS(B:B, A:A, ">="&E1, A:A, "<="&F1)

SUMIF vs SUMIFS: When to Use Each

Common SUMIF Edge Cases

• Data type mismatch: Numbers stored as text won't match numeric criteria. Use VALUE() or TEXT() to align types.
• Sum_range sizes differ: If sum_range is smaller than range, SUMIF only sums the overlapping top-left portion. Always match sizes.
• Leading/trailing spaces: =SUMIF(A:A, "Apple", B:B) won't match " Apple". Use TRIM() on your data or criteria: "="&TRIM(E1).
• Case sensitivity: SUMIF is not case-sensitive. "APPLE", "Apple", and "apple" all match.
• Criteria longer than 255 characters: SUMIF rejects criteria strings longer than 255 characters. For longer patterns, use a helper column or switch to SUMIFS with SUMPRODUCT.