Summary

The Excel SUMIF function sums values that meet one condition. It tests a range against a criterion and, for matching rows, adds the corresponding values from the sum range. This makes SUMIF the natural choice when a worksheet needs one conditional total rather than a grand total.

SUMIF can work with text criteria, numeric comparisons, wildcards, and cell-based conditions. That flexibility is why it appears so often in category reports, exception summaries, and threshold analysis. If the worksheet needs more than one condition, the formula usually moves to SUMIFS.

Syntax

=SUMIF(range, criteria, [sum_range])

range is the set of cells tested against the condition. criteria is the condition itself. sum_range is optional; if omitted, Excel sums the cells in range directly. Microsoft's documentation also notes that criteria may be text, numbers, expressions, or references.

Arguments

• range - [Required] The cells to evaluate against the criterion.
• criteria - [Required] The condition to apply. This can be text, a number, an operator-based expression, or a cell reference.
• sum_range - [Optional] The cells to add when the condition is met.

SUMIF vs Related Functions

SUMIF is for one condition only. If the reporting question becomes "sum where this and that," the formula usually needs SUMIFS instead.

One useful point from Microsoft's guidance is that SUMIF is not case-sensitive. If case matters, another approach is needed. In most worksheet reporting, however, case-insensitive matching is appropriate and more convenient.

Using SUMIF

SUMIF is most effective when a worksheet contains one flat table and the report needs a subtotal for one chosen category. That allows the source data to remain centralized while category totals are derived in summary cells rather than separated into multiple sheets or manual filters.

The function is also practical for threshold logic. For example, a finance sheet might need the total of all transactions greater than a stated amount. In that case, the condition is numeric rather than textual, but the worksheet logic remains the same: test each row, and add only the rows that qualify.

• Use quoted operators for numeric conditions such as ">200".
• Use a cell reference when the criterion should change dynamically.
• Use <> when the requirement is exclusion rather than inclusion.

Example 1 - Sum Sales for One Region

This is the standard text-based SUMIF pattern. The formula scans the region column, and when a row contains "North", it adds the corresponding sales value from column C. Rows with any other region label are ignored.

=SUMIF(B1:B5,"North",C1:C5)
// Matching rows: C1 = 150 and C3 = 300
// Total = 450

Example 2 - Sum Only Values Greater Than 200

Here the tested range and the summed range are the same, so the third argument can be omitted. The criterion includes the comparison operator inside quotes, which is the required syntax for operator-based conditions.

=SUMIF(C1:C5,">200")
// Matching values: 300 and 300
// Total = 600

Example 3 - Use a Cell as the Criterion

Cell-driven criteria make the formula reusable. Instead of hard-coding a category, the worksheet reads the desired label from G1. This pattern is common in report selectors and dropdown-driven summaries.

=SUMIF(B1:B5,G1,C1:C5)
// G1 = "South"
// Matching value: C2 = 300

Example 4 - Exclude One Category

The not-equal operator in SUMIF allows the formula to total all rows except those matching one excluded label. This is often simpler than subtracting one subtotal from a grand total, especially when the report logic should remain explicit.

=SUMIF(B1:B5,"<>North",C1:C5)
// Included rows: South = 300, East = 50, West = 200
// Total = 550

Date-based SUMIF formulas deserve extra care. If the criterion involves a date comparison, it is safer to build the date with DATE() rather than rely on regional text formatting. That keeps the worksheet logic consistent across systems with different locale settings.

• SUMIF is not case-sensitive.
• Quoted operators are required for numeric comparisons.
• Use DATE() when building date criteria to avoid text-format ambiguity.

Conclusion Recap

• Summary: SUMIF sums values that meet one condition.
• Syntax: =SUMIF(range,criteria,[sum_range]).
• Key behavior: The function supports text, numeric, wildcard, and reference-based criteria.
• Practical usage: Category totals, threshold summaries, exclusions, and dynamic single-filter reporting.