SUMIF Excel Sum Range: How It Works and Examples

Formulas & functions · Updated November 18, 2026

In Excel, SUMIF adds values that meet a condition: its range argument is where Excel checks the condition, and its optional sum_range argument is the cells it adds. For example, with product names in A2:A6 and sales amounts in B2:B6, =SUMIF(A2:A6,"Hardware",B2:B6) totals the sales for rows labeled Hardware.

What “sum range” means in SUMIF

The phrase “SUMIF sum range” can refer to two related arguments that are easy to confuse. The first argument, range, tells Excel which cells to evaluate. The third argument, sum_range, tells it which corresponding cells to add when the condition is met. The criteria can be text, a number, a comparison, or a wildcard pattern.

Think of the two ranges as paired rows. If A2 contains Hardware and B2 contains 120, then a condition matching A2 includes the value in B2 in the total. The condition is checked in column A; the amount is taken from column B. The sum range does not have to be beside the criteria range, but both ranges should cover the same rows and have the same shape.

The third argument is optional. When you leave it out, Excel adds the cells in the first argument that meet the condition. That is useful when the values being tested are also the values you want to total. When the condition is in one column and the amounts are in another, specify the sum range.

SUMIF syntax and its three arguments

In Microsoft Excel for Microsoft 365 on Windows, SUMIF takes a criteria range, a criterion, and, optionally, a sum range. In order, the arguments mean:

  • range: The cells Excel checks against the criterion.
  • criteria: The condition that determines which cells qualify. It can be a number, text, expression, cell reference, or wildcard pattern.
  • sum_range: The cells Excel adds for the qualifying positions. If omitted, Excel adds qualifying cells from the first argument instead.

For example, a criterion of Hardware matches cells containing that text, while a criterion of >100 matches numbers greater than 100. Text criteria and comparison operators written directly in a formula need quotation marks; a cell reference containing the criterion does not. The formula examples here use the standard comma argument separator. Some regional settings use semicolons instead, so Excel may show a different separator on a computer configured for another list-separator convention.

A worked example with a separate sum range

Suppose A2:A6 contains Hardware, Software, Hardware, Services, and Hardware, in that order. B2:B6 contains 120, 80, 45, 200, and 35. To total the amounts on Hardware rows, check column A and add the corresponding cells in column B. With those labels in A2:A6 and amounts in B2:B6, enter:

=SUMIF(A2:A6,"Hardware",B2:B6)

The result is 200: Excel adds 120, 45, and 35. It does not add the amounts beside Software or Services. If you put Hardware in D2 instead, you can refer to that cell as the condition. With the same labels and amounts, and Hardware in D2, use:

=SUMIF(A2:A6,D2,B2:B6)

This also returns 200. A cell-based criterion is handy when a report user needs to choose a department, product, or region without editing the formula itself. You can copy the formula down or across in a report, taking care to use absolute references where ranges need to stay fixed.

When you can omit the sum range

If the values you want to add are also the values you are testing, omit the third argument. For instance, the following example checks each number in B2:B6 and adds it only when it is greater than 100. Assume B2:B6 contains 120, 80, 45, 200, and 35:

=SUMIF(B2:B6,">100")

The result is 320, because 120 and 200 are the only values greater than 100. This is different from a formula that checks one range and totals another: without a sum range, Excel does not look elsewhere for amounts to add.

You can also build a comparison criterion using a cell reference. If B2:B6 contains the same five amounts and D2 contains 100, use:

=SUMIF(B2:B6,">"&D2)

That returns 320 as well. The ampersand joins the greater-than operator to the threshold in D2, creating the criterion Excel evaluates. This approach is convenient when thresholds change regularly, such as a minimum order amount or a sales target.

Text, wildcards, and comparison criteria

SUMIF can match text exactly or use wildcard characters to match a pattern. A criterion such as Hardware matches that text; it does not normally match Hardware supplies because the latter contains extra characters. To match text that starts with Hardware, use an asterisk after the word. With product names in A2:A20 and amounts in B2:B20, the formula is:

=SUMIF(A2:A20,"Hardware*",B2:B20)

The asterisk represents any number of characters, including none. A question mark represents one character. To search for an actual asterisk or question mark rather than use it as a wildcard, put a tilde before it. These patterns are useful for category labels with suffixes, but they can also match more rows than intended, so check that the wording is distinctive enough.

For numeric conditions, SUMIF accepts comparison operators such as greater than, less than, and not equal to. Put an operator and a fixed number together in the criterion text, or join the operator to a cell reference as in the previous example. Dates are stored as numbers in Excel, so date conditions can also be constructed with comparison operators. For a period with both a start and end boundary, however, SUMIFS is usually clearer because it allows multiple criteria.

Keep the criteria range and sum range aligned

The most important setup check is that corresponding cells refer to the same records. If A2:A100 contains regions and C2:C100 contains revenue, each region in row 2 must describe the revenue in C2. Using A2:A100 with C3:C101 shifts the pairing by one row: a qualifying region could cause Excel to add a different record’s revenue.

Use ranges with matching dimensions whenever possible. Excel pairs positions starting from the top-left cells of the ranges, so a mismatched size can make results surprising even when the formula is accepted. Avoid including a heading in just one of the ranges, and make sure inserted rows or copied formulas have not moved one range but not the other.

Whole-column references can be convenient in a growing list, such as checking A:A and summing B:B, but they make Excel consider many more cells than a focused range. For an ordinary worksheet table, matching ranges such as A2:A5000 and B2:B5000 are often easier to review. If the data is in an Excel table, structured references can make formulas more readable and help ranges expand as table rows are added.

Common SUMIF errors and how to check them

A formula can return zero or an unexpected total even when its syntax is valid. Check the following common causes before rebuilding it:

  • Wrong argument order: The first range is the one Excel tests, not the cells to total. Put the amount cells in the third argument when they are in a separate column.
  • Extra spaces or different labels: “East” and “East ” may not behave as the same text. Check the source data for inconsistent spelling, spaces, or abbreviations.
  • Numbers stored as text: Values that look numeric may be text, which can prevent totals or numeric comparisons from behaving as expected. Check how the values were imported and convert them to numbers if appropriate.
  • Criterion entered as text incorrectly: A comparison operator typed directly into a criterion needs to be part of a quoted string, such as ">100". When the threshold comes from a cell, join the operator and cell reference with &.
  • Misaligned ranges: Confirm that both ranges start on the same record and cover corresponding rows.
  • Unexpected wildcard matches: A pattern such as "A*" can include every label beginning with A, not just one intended category.

When investigating a total, filter or inspect the source rows that should qualify, then add their amounts manually. This simple check can reveal whether the issue is the criterion, the selected sum range, or the underlying data. Also check that the formula cell is formatted to display a useful number of decimal places; formatting affects how a result appears, not which values SUMIF adds.

SUMIF compared with SUMIFS

SUMIF is designed for one condition. For example, it can total revenue for Hardware or total transactions over a threshold. If you need to satisfy more than one condition—such as a product and a region, or dates within a start-and-end period—use SUMIFS. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria. This reversed placement of the sum range is a common source of confusion when switching between the two functions.

SUMIFS applies all its criteria together, so a row must meet every specified condition to be included. It is not the same as adding separate SUMIF formulas when you want an “either/or” result, because combining formulas can count overlapping rows more than once. For one condition, SUMIF is usually the simpler choice; for several conditions on matching records, SUMIFS is generally easier to audit.

Frequently Asked Questions

What is the sum_range in an Excel SUMIF formula?
The sum_range is the optional set of cells Excel adds when the corresponding cell in the criteria range meets the condition. It is the third argument when the amounts are in a separate range.

Can the SUMIF range and sum_range be different sizes?
They should be the same size and shape for clear, predictable results. Excel pairs positions from the ranges’ starting cells, so different dimensions can lead to confusing results. Match their rows and columns whenever you can.

What happens if I leave sum_range blank?
Excel adds qualifying cells from the first range. This is appropriate when the condition is applied to the same numbers you want to total, but not when the amounts are stored in another column.

Why does my SUMIF return zero?
Check that the criteria match the source values, the condition is written correctly, and the sum range points to numeric amounts on the matching rows. Extra spaces, inconsistent labels, text-formatted numbers, and shifted ranges are frequent causes.

Can SUMIF add values based on two conditions?
SUMIF handles one condition. Use SUMIFS when every row must meet multiple conditions, such as matching both a department and a month. SUMIFS uses the sum range as its first argument, unlike SUMIF.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.