SUMIF Excel Sample Data: Examples That Work

Formulas & functions · Updated November 18, 2026

For a basic SUMIF Excel sample, put item names in A2:A6 and amounts in B2:B6; to total Apples, use =SUMIF(A2:A6,"Apples",B2:B6), which returns 25. The sample data and variations below show how to total values by text, numbers, dates, and criteria stored in cells.

Basic SUMIF sample data

Enter this small dataset in a blank worksheet, starting in cell A1. The first row contains headings; the SUMIF ranges in the examples below start in row 2 so the headings are not included.

Item Amount
Apples 12
Oranges 7
Apples 13
Pears 8
Oranges 5

With the item names in A2:A6 and amounts in B2:B6, the formula =SUMIF(A2:A6,"Apples",B2:B6) returns 25: the two Apple rows contain 12 and 13, and 12 + 13 = 25. The same structure works for a sales list, expense log, inventory report, or any table where one column identifies a group and another holds values to add.

In Microsoft Excel for Microsoft 365 on Windows, select a blank cell, type the formula, and press Enter. You can paste the sample table directly into a worksheet. On Mac and Excel for the web, the function and formula syntax are the same. Some regional settings use semicolons instead of commas between formula arguments; use the separator Excel expects in your installation.

What SUMIF does and how its arguments work

SUMIF adds numbers associated with entries that meet one condition. Its three arguments are the range Excel checks, the criterion it tests for, and the range of numbers it adds. In the sample, A2:A6 is the check range, Apples is the condition, and B2:B6 is the amount range.

The third argument, sum_range, is optional. If you leave it out, Excel adds the cells in the range it checked. That is useful when the values being tested are also the values to total, such as adding every quantity greater than a threshold. When the condition and values are in different columns, include sum_range so Excel adds the intended numbers rather than the text or category column.

SUMIF evaluates one criterion at a time. It is not case-sensitive for ordinary text comparisons, so Apples and APPLES match the same entries. It can match exact text, comparison operators, wildcards, and dates. The criterion can be typed into the formula or referenced from a worksheet cell, which is often more convenient for a reusable report.

Use a cell as the criterion

Typing the condition directly into a formula is convenient for a one-off total. For a report that users may change, put the requested item in a separate cell. For example, enter Apples in D2. With item names in A2:A6, the selected criterion in D2, and amounts in B2:B6, =SUMIF(A2:A6,D2,B2:B6) returns 25.

Referencing D2 means you can change the requested item without editing the formula. If D2 is changed to Oranges, the result becomes 12, from 7 + 5. You can copy the formula down a report beside a list of categories. With item names in A2:A6, criteria in D2:D4, and amounts in B2:B6, use =SUMIF($A$2:$A$6,D2,$B$2:$B$6) in E2 and fill it down; for criteria Apples, Oranges, and Pears, the results are 25, 12, and 8. The dollar signs keep the source ranges fixed while D2 changes to D3 and D4.

Sum values that meet a numeric condition

SUMIF can compare numbers as well as match labels. Comparison operators such as greater than, less than, and greater than or equal to are entered as part of the criterion text. When the operator is typed directly into a formula, enclose it in quotation marks. In the original sample, with amounts in B2:B6, =SUMIF(B2:B6,">=10") returns 25 because only 12 and 13 meet the condition. The sum_range argument is omitted because Excel is checking and adding the same cells.

A cell can hold the threshold, too. Suppose F2 contains the number 10. With amounts in B2:B6 and the threshold in F2, =SUMIF(B2:B6,">="&F2) returns 25. The ampersand joins the text operator to the value in F2, so raising or lowering the threshold updates the result. This is a common pattern for totals above a target, quantities below a reorder point, or expenses at least as large as a chosen amount.

Do not put a comparison operator inside the threshold cell if the formula already adds the operator. For example, keep F2 as the number 10, not the text >=10. Otherwise the formula may build an invalid or unintended criterion.

Match partial text with wildcards

Wildcards let a text criterion match a pattern instead of one complete label. The asterisk (*) stands for any number of characters, including no characters, while the question mark (?) stands for one character. A tilde (~) can be used before a wildcard when you need to search for that character itself rather than use it as a pattern.

With item names in A2:A6 and amounts in B2:B6, =SUMIF(A2:A6,"Ap*",B2:B6) returns 25. The pattern Ap* matches labels beginning with Ap, such as Apples and Apricots, so it can be useful when a list contains product names with shared prefixes. The result depends on the actual labels in the range; it does not create or infer categories beyond matching the text pattern.

Use wildcards thoughtfully. A criterion of *berry can match any text ending in berry, while * can match any text entry, making a broad total easy to produce accidentally. If you need an exact category total, use the complete label or a cell containing that label instead. Ordinary SUMIF text matching ignores capitalization, so wildcard searches do too.

Sum records on or after a date

Dates in Excel are stored as serial values, so use real Excel dates in the worksheet rather than text that only looks like a date. A small date sample might have transaction dates in A2:A5—December 15, 2025; January 5, 2026; January 20, 2026; and February 2, 2026—and sales in B2:B5 of 40, 60, 25, and 15, respectively.

With dates in A2:A5 and sales amounts in B2:B5, =SUMIF(A2:A5,">="&DATE(2026,1,1),B2:B5) returns 100. The formula adds 60, 25, and 15 for dates on or after January 1, 2026. DATE builds a date value from year, month, and day, avoiding ambiguity between regional date formats. If your criteria date is in a worksheet cell, join the comparison operator to that cell instead. For totals within a date interval, one SUMIF can apply a lower boundary, but a second condition is needed for the upper boundary; SUMIFS is usually clearer for that task.

Common SUMIF problems and how to fix them

If a formula returns zero or a result that seems too small, first check whether the text in the criterion matches the source labels. Extra spaces, different punctuation, or a misspelled category can prevent an exact match. Also confirm that the numbers in the sum range are numeric values rather than numbers stored as text. Text-formatted numbers may not be added as expected.

  • Check range alignment: the criteria range and sum range should cover corresponding rows and normally have the same shape. If one starts at row 2 and the other starts at row 3, values can be associated with the wrong records.
  • Exclude headers: including a heading usually does not harm a numeric total, but it makes the selected range less clear and can cause problems if the table changes.
  • Check the criterion syntax: a text criterion such as Apples needs quotation marks when typed directly in a formula. A comparison such as >=10 needs the operator and number together inside the criterion text.
  • Verify dates are dates: changing a cell’s display format does not convert text into an Excel date. Re-enter or convert text dates if a date condition does not include the expected rows.
  • Inspect copied formulas: use dollar signs on ranges when copying a formula down, but leave the criterion reference relative if it should move to the next row.

SUMIF does not distinguish text by capitalization. If the requirement is to total only entries whose capitalization differs, SUMIF alone is not the right tool. Likewise, a criterion range should be a worksheet range; SUMIF is not a general-purpose way to test a calculation or multiple independent conditions at once.

SUMIF versus SUMIFS

Use SUMIF when one condition is enough, such as adding every sale for Apples. Use SUMIFS when each included row must satisfy two or more conditions, such as a product and a region. SUMIFS is available in current Excel versions, including Microsoft 365, and places the sum range first, followed by pairs of criteria ranges and criteria. That argument order differs from SUMIF, so check it when switching functions.

For a two-condition example, enter Product in A1, Region in B1, and Sales in C1, with these records in rows 2 through 7: Apples, North, 12; Apples, South, 9; Oranges, North, 7; Apples, North, 13; Pears, South, 8; Oranges, South, 5. With products in A2:A7, regions in B2:B7, and sales in C2:C7, =SUMIFS(C2:C7,A2:A7,"Apples",B2:B7,"North") returns 25, adding only the two Apple sales from the North region. SUMIF cannot express both conditions in one ordinary criterion test; SUMIFS is the more direct choice.

Frequently Asked Questions

What is the simplest SUMIF formula in Excel?
With criteria in A2:A6 and values to add in B2:B6, use =SUMIF(A2:A6,"Apples",B2:B6) to total rows labeled Apples. Replace the ranges and criterion with the locations and value in your own dataset.

Can SUMIF add values based on a number in another cell?
Yes. Put the threshold in a cell and combine a comparison operator with that cell. For example, with values in B2:B6 and a threshold in F2, =SUMIF(B2:B6,">"&F2) adds values greater than the number in F2.

Why does SUMIF return zero?
Common causes include a criterion that does not match the source text, numbers stored as text, a date stored as text, or a criteria range that does not cover the expected rows. Check the actual cell values and make sure the formula points to the intended ranges.

Does SUMIF support more than one condition?
SUMIF is designed for one condition. Use SUMIFS when a total must meet multiple conditions, such as matching both a product and a region. SUMIFS takes the sum range first, unlike SUMIF.

Is SUMIF case-sensitive?
No. Standard SUMIF text matching is not case-sensitive, so capitalization differences alone do not separate otherwise identical labels. Wildcard text criteria are also not case-sensitive.

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.