SUMIF Function

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.

SUMIF is useful when the workbook needs one targeted total rather than a full total of everything. It combines logic and arithmetic neatly, which makes reports and category-based summaries much easier to read and maintain.

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.

This is a strong beginner example because it shows the main job of SUMIF very clearly: check one condition first, then total only the matching rows.

=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.

This makes the example useful for threshold reporting. The sheet is not grouping by a label here, but by a numeric rule, which is another common SUMIF pattern.

=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.

This is practical because the summary can change without rewriting the formula. The user only changes the criterion cell, and the total updates automatically.

=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.

This helps show that SUMIF can work as an inclusion or an exclusion tool. The formula is still using one condition, but now that condition tells Excel what to leave out.

=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

SUMIF is the next step after a normal SUM when you only want part of the data included. This lesson showed that the function works by checking one condition first, then adding only the rows that match that condition.

The examples covered the main beginner patterns you will use most: one category, one threshold, one value from a cell, or one item to exclude. If the worksheet question is still based on just one filter, SUMIF is usually the cleanest answer.

• 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.