SUMIF in Excel: Formula, Examples, and Common Fixes

Formulas & functions · Updated November 21, 2026

To total values that meet one condition in Excel, use SUMIF. With product names in A2:A6 and amounts in B2:B6, enter =SUMIF(A2:A6,"Apples",B2:B6) to add the amounts for Apples; if the matching amounts are 12, 15, and 5, the result is 32.

What SUMIF does

SUMIF checks one range for a condition, then adds the corresponding values from another range. It is useful for questions such as “How much revenue came from the East region?” or “What is the total for items marked Paid?” The condition can match text, a number, a comparison, a date, or a pattern.

The function has three arguments: the range Excel checks, the criterion to apply, and the range to sum. The sum range is optional. If you leave it out, Excel adds the cells in the criteria range itself. The criteria range and sum range should have the same dimensions and correspond row by row; otherwise, results can be unexpected.

In Microsoft 365 for Windows, type a formula in a blank cell and press Enter. SUMIF works in current Excel for Mac and Excel for the web as well, though ribbon and interface details can differ. Formulas below use commas between arguments, as in a standard English-language installation. Some regional settings use semicolons instead.

Build a basic SUMIF formula

Start with a simple two-column list: a label in one column and its amount in another. For example, suppose A2:A6 contains Apples, Pears, Apples, Oranges, and Apples, while B2:B6 contains 12, 8, 15, 9, and 5. To total the Apples rows, use this formula in any empty cell:

With product names in A2:A6 and their amounts in B2:B6, enter =SUMIF(A2:A6,"Apples",B2:B6). Excel checks each product name and adds the amount on the same row whenever the name matches. The result is 32.

It is often more flexible to put the criterion in a cell. If E2 contains the word Apples, this formula refers to that cell rather than repeating the word inside the formula:

With product names in A2:A6, amounts in B2:B6, and the requested product in E2, enter =SUMIF(A2:A6,E2,B2:B6). The result is 32 when E2 contains Apples. Change E2 to Pears and the same formula returns 8. This setup is especially handy for summary tables and dropdown-driven reports.

To add numbers in the same range that is being checked, omit the third argument. For instance, with numeric values in C2:C20, the formula =SUMIF(C2:C20,">100") totals only the values greater than 100. The criterion range is also the sum range in this form.

Use text, numbers, and comparison criteria

A criterion can be an exact text value such as “Paid” or a number such as 10. For a comparison, put the operator and value together inside quotation marks. The supported comparison operators include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), not equal to (<>), and equal to (=).

For a fixed comparison, with quantities in A2:A20 and costs in B2:B20, enter =SUMIF(A2:A20,">10",B2:B20) to add costs for rows whose quantity is greater than 10. The value 10 is excluded. For greater than or equal to 10, use the operator >= inside the criterion.

To base a comparison on a value that can change, join the operator to a cell reference. With quantities in A2:A20, costs in B2:B20, and the threshold in E2, use =SUMIF(A2:A20,">"&E2,B2:B20). If E2 contains 10, this sums costs for quantities above 10. The ampersand joins the comparison text to the value in E2; typing the criterion as a quoted string containing the cell address would not make Excel read that cell.

Text matching is generally not case-sensitive: “paid” and “Paid” match the same entries. SUMIF also supports two wildcard characters. An asterisk (*) stands for any number of characters, including none, and a question mark (?) stands for one character. For example, with descriptions in A2:A50 and amounts in B2:B50, =SUMIF(A2:A50,"*shipping*",B2:B50) adds amounts where the description contains “shipping.” To match an actual asterisk or question mark in the text, place a tilde (~) before the character in the criterion.

Sum values for a date or date period

Excel stores dates as serial numbers, so SUMIF can compare dates using the same operators as numbers. A reliable approach is to construct criteria from DATE, rather than typing a date string that might be interpreted differently under another regional date setting.

With transaction dates in A2:A20 and amounts in B2:B20, =SUMIF(A2:A20,">="&DATE(2026,1,1),B2:B20) adds amounts dated January 1, 2026, or later. This is an open-ended condition, not a total for January alone. To sum one month, use two conditions: one for the first day of the month and another for the first day of the following month. SUMIFS, which handles multiple conditions, is suited to that task and is explained below.

Check that the cells contain real Excel dates, not text that merely looks like dates. A date stored as text may not compare as expected. Applying a date number format changes how a valid date is displayed, but it does not convert text into a date value.

When to use SUMIFS instead

SUMIF accepts one condition. When a total must satisfy two or more conditions at the same time, use SUMIFS. Its argument order differs: the sum range comes first, followed by pairs of criteria ranges and criteria. Each condition must be true for a row to be included.

For example, suppose A2:A5 contains East, East, West, East; B2:B5 contains Paid, Pending, Paid, Paid; and C2:C5 contains 120, 40, 90, 65. With regions in A2:A5, payment statuses in B2:B5, and sales in C2:C5, enter =SUMIFS(C2:C5,A2:A5,"East",B2:B5,"Paid"). It returns 185, adding 120 and 65 while excluding the pending East sale and the West sale.

The same pattern can set the beginning and end of a date period. With dates in A2:A20 and amounts in B2:B20, use =SUMIFS(B2:B20,A2:A20,">="&DATE(2026,1,1),A2:A20,"<"&DATE(2026,2,1)) to sum January 2026, including January 1 but excluding February 1. Using a less-than condition for the next month’s first day also includes any valid times stored alongside the dates on January 31.

Common SUMIF problems and how to fix them

  • The total is zero. Check spelling, extra spaces, and whether the criterion is in the range being checked. Confirm that the sum range contains numeric values rather than numbers stored as text. A criterion cell reference may also point to an empty or incorrect cell.
  • The total is too high or too low. Make sure the criteria and sum ranges start and end on the same rows. If one range includes a header or an extra row, the row-by-row matches may not line up. Check the criterion operator too: >10 does not include 10, while >=10 does.
  • A comparison does not work as intended. Operators such as >= must be included in the criterion string, or joined to a cell reference with &. Avoid putting a comparison operator in a separate cell unless the formula joins it correctly to the threshold.
  • A date condition misses records. Verify that the source values are genuine dates and that the comparison uses the right boundary. A condition for dates on or after January 1 will include every later date unless a second boundary is added.
  • The formula shows an error or will not enter. Check that parentheses and quotation marks are balanced and that the range references use the correct sheet. If the computer’s list separator is a semicolon, replace argument commas with semicolons. Do not change commas that are part of a number format or text value.

For large workbooks, avoid selecting whole columns unnecessarily if calculation speed is a concern. A bounded range such as A2:A5000 makes the intended data area clear. Excel tables can also make formulas easier to maintain as rows are added, because table references expand with the data.

SUMIF compared with a PivotTable

SUMIF is a good choice for a fixed summary cell, a small report, or a total controlled by a criterion cell. It keeps the calculation visible in the worksheet and updates when source values change. SUMIFS is a natural extension when the report needs multiple simultaneous conditions.

A PivotTable is often more convenient when the goal is to explore totals by several categories, compare groups, or rearrange a report without writing a separate formula for every combination. It can summarize larger datasets interactively, but it has a different workflow and may need refreshing after certain source-data changes. Choose SUMIF for a direct formula-driven result; choose a PivotTable when flexible grouping and exploration matter more than a single targeted calculation.

Frequently Asked Questions

What is the difference between SUMIF and SUMIFS?
SUMIF applies one criterion, while SUMIFS can apply multiple criteria that must all match. Their argument order is different: SUMIF starts with the criteria range, whereas SUMIFS starts with the range to sum.

Can SUMIF add cells based on partial text?
Yes. Use an asterisk wildcard before and/or after the text for any number of characters, or a question mark for one character. For example, a criterion like *east* can match a cell containing East region.

Does SUMIF distinguish uppercase and lowercase?
No. Standard SUMIF text comparisons are not case-sensitive. If case-sensitive matching is essential, SUMIF alone is not the right tool; a formula using other functions or a different data-cleaning approach is needed.

Why does SUMIF return zero when the cells look like numbers?
The amounts may be stored as text, which SUMIF does not add as numeric values. Convert the source entries to numbers, and check that the criterion actually matches the intended rows. Imported data can also contain hidden spaces or inconsistent text.

Can I use SUMIF for a date range?
SUMIF can apply one date boundary, such as on or after a chosen date. A bounded period such as a month needs two conditions, so SUMIFS is typically the clearer choice: use a start date with >= and the next period’s start with <.

Make summaries easier to maintain

For repeatable reports, keep source data in consistent columns with one record per row, use a criterion cell instead of embedding a value in every formula, and ensure amounts and dates are stored in their proper data types. Label the result cell so readers know what the total represents. These small habits make a SUMIF easier to audit and reduce errors when the worksheet grows.

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.