SUMIF and SUMIFS Examples in Excel

In Excel, use SUMIF to add values that meet one condition and SUMIFS to add values that meet multiple conditions. With categories in A2:A8 and amounts in C2:C8, the one-condition formula is =SUMIF(A2:A8,"Fruit",C2:C8); with regions in B2:B8 as well, the two-condition version is =SUMIFS(C2:C8,A2:A8,"Fruit",B2:B8,"East").

A small dataset for the examples

The examples below assume a simple sales table. Column A contains a category, column B a region, and column C a sales amount. The rows are Fruit, East, 120; Fruit, West, 90; Vegetables, East, 150; Fruit, East, 80; Vegetables, West, 110; Fruit, East, 70; and Fruit, West, 60. The header row is row 1, and the data runs from row 2 through row 8.

This gives the examples checkable results: fruit sales total 420, fruit sales in the East total 270, and sales above 100 total 380. If your columns or rows differ, replace the example ranges with the corresponding ranges in your own worksheet. The ranges in these formulas deliberately exclude the headers.

SUMIF examples for one condition

SUMIF has three arguments: the range to check, the condition (called the criteria), and the range to add. The sum range is optional when the values to check are also the values to add. For example, it can sum amounts whose value is greater than a threshold.

With categories in A2:A8 and amounts in C2:C8, this sums sales for Fruit and returns 420:

=SUMIF(A2:A8,"Fruit",C2:C8)

With amounts in C2:C8, this adds only amounts greater than 100. It returns 380 because 120, 150, and 110 qualify:

=SUMIF(C2:C8,">100",C2:C8)

You can put a criterion in a worksheet cell instead of typing it into the formula. With categories in A2:A8, amounts in C2:C8, and the word Fruit in E2, this formula also returns 420:

=SUMIF(A2:A8,E2,C2:C8)

Cell-based criteria make a summary easier to reuse: change E2 to Vegetables and the result updates. They also help avoid editing a formula every time a report needs a different category.

SUMIFS examples for multiple conditions

SUMIFS takes the sum range first, followed by one or more pairs of criteria range and criterion. Every pair adds another condition, and all conditions must be true for a row to contribute to the total. This is an AND relationship: a row must match the category and the region.

With amounts in C2:C8, categories in A2:A8, and regions in B2:B8, this sums fruit sales in the East and returns 270 (120 + 80 + 70):

=SUMIFS(C2:C8,A2:A8,"Fruit",B2:B8,"East")

Criteria can also refer to cells. With the same data ranges, Fruit in E2, and East in F2, this returns the same 270:

=SUMIFS(C2:C8,A2:A8,E2,B2:B8,F2)

To add another condition, append another criteria-range-and-criterion pair. For example, a larger table could include a salesperson or product type, and a SUMIFS formula could require the chosen category, region, and salesperson all to match. Keep each criteria range aligned row for row with the sum range so that Excel evaluates the conditions against the same records.

Criteria with comparisons, dates, and text

Comparison criteria are written as text, using operators such as >, >=, <, <=, or <> (not equal). When an operator is combined with a value in another cell, join them with &. With amounts in C2:C8, E2 containing 100, and the amounts also serving as the values to add, this returns 380:

=SUMIF(C2:C8,">"&E2,C2:C8)

For a date range, use two conditions against the date column: one on or after the start date and one before the day after the end date. This approach includes every time recorded on the final date, if the source cells contain times as well as dates. Suppose transaction dates are in A2:A100, amounts are in B2:B100, the start date is in D2, and the final date is in D3. The formula is:

=SUMIFS(B2:B100,A2:A100,">="&D2,A2:A100,"<"&(D3+1))

For text matching, criteria can include the wildcards * (any number of characters) and ? (one character). With product descriptions in A2:A100 and amounts in B2:B100, this sums rows where the description ends in “berry”:

=SUMIF(A2:A100,"*berry",B2:B100)

To match a literal asterisk or question mark rather than use it as a wildcard, precede that character with a tilde. Text matching with these functions is not case-sensitive, so Fruit and FRUIT match the same criterion.

How to choose between SUMIF and SUMIFS

Use SUMIF when one condition is enough, such as total sales for a product or expenses above a set amount. Use SUMIFS when the total depends on two or more conditions, such as a product in a particular region during a reporting period. SUMIFS can also handle a single condition, but its argument order differs from SUMIF: SUMIF starts with the condition range, while SUMIFS starts with the range to add.

For a choice between two possible values, remember that multiple SUMIFS conditions are AND, not OR. To sum fruit sales from either the East or West, add the two separate results. With amounts in C2:C8, categories in A2:A8, and regions in B2:B8, this returns 420:

=SUM(SUMIFS(C2:C8,A2:A8,"Fruit",B2:B8,"East"),SUMIFS(C2:C8,A2:A8,"Fruit",B2:B8,"West"))

That total includes all fruit sales because the example has only East and West entries. For longer lists of alternatives, consider whether a helper column or a PivotTable would make the logic clearer to maintain.

Common problems and how to fix them

If a total is zero or unexpectedly small, check the conditions and ranges before rebuilding the formula. These checks resolve many everyday errors:

Range alignment: The sum range and each criteria range should cover corresponding rows and the same number of cells. Avoid a sum range starting on row 2 paired with a criteria range starting on row 3.
Text that looks numeric: A number stored as text may not behave like a numeric amount in a comparison. Check the source data, and convert text-form numbers to actual numbers when appropriate.
Extra spaces or inconsistent labels: “East” and “East ” may appear identical but are different text values. Clean inconsistent source entries or use a consistent data-entry list.
Criteria punctuation: A comparison operator in a criterion must be included in the quoted text, and an operator joined to a cell reference needs &.
Dates stored as text: A date-looking string may not be a real Excel date value. Check that the cells contain dates Excel recognizes, not just text formatted to look like dates.
Headers included in ranges: Usually start the ranges at the first data row. Including a text header generally will not add to a numeric sum, but consistent ranges make formulas easier to inspect and copy.

When copying a formula down a summary table, consider whether any references should stay fixed. For example, a criterion cell reference such as E2 normally changes to E3 in the next row, which is useful when each row has a different criterion. If a reference must remain fixed, use Excel’s dollar-sign reference notation, such as $E$2.

Alternatives for more complex summaries

SUMIF and SUMIFS are useful for straightforward conditional totals, but they are not the only way to summarize data. A PivotTable can group and total a longer list interactively, which may be more convenient when readers need to filter by several fields or compare categories. A helper column can make complicated row-level logic visible before the amounts are totaled. For a small, fixed summary, SUMIFS is often easier to review directly in a cell.

Choose based on how the workbook will be used: formulas are convenient when the summary needs to update automatically in a defined layout; a PivotTable can be easier to explore; and helper columns can make unusual conditions more transparent. Regardless of method, clear headers, consistent data types, and a single row per record make reliable totals much easier.

Frequently Asked Questions

Can SUMIFS use just one condition?

Yes. SUMIFS accepts one or more criteria pairs, so a single pair is valid. Remember that its first argument is the sum range, unlike SUMIF, whose first argument is the criteria range.

Are SUMIF and SUMIFS case-sensitive?

No. Their text criteria are not case-sensitive. If you need case-sensitive conditional totals, these functions alone do not provide that distinction; use a different approach that explicitly tests text case.

Can I use a cell reference as the criterion?

Yes. Put the desired value in a cell and use that cell as the criterion argument. For a comparison such as greater than the value in E2, combine the operator and reference with an ampersand, as in the example above.

Why does SUMIFS return zero when matching rows are visible?

Check for extra spaces, mismatched date or number types, incorrect range alignment, and criteria that differ from the underlying values. A cell can display a date or number while actually containing text, so verify the stored data as well as its appearance.

Can SUMIFS add rows that match either of two criteria?

Multiple criteria pairs in one SUMIFS formula require all the conditions to be true. For an either/or choice, add separate SUMIFS results, as in the East-or-West example, or use another summary method suited to a larger set of alternatives.

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.