SUMIF Example Formula: Syntax, Uses, and Common Fixes

With product names in A2:A7 and sales amounts in B2:B7, use =SUMIF(A2:A7,"Apples",B2:B7) to add the sales for Apples; if those rows contain 12, 8, 15, 6, 11, and 9 respectively, the result is 36. SUMIF is an Excel function for adding numbers that meet one condition.

A simple SUMIF example

Imagine a small sales list in which column A identifies the product and column B records the amount sold. The rows are Apples—12, Oranges—8, Apples—15, Bananas—6, Oranges—11, and Apples—9. The formula checks each product name and adds the amount from the same row whenever the name is Apples.

With product names in A2:A7 and sales amounts in B2:B7, this formula returns 36:

=SUMIF(A2:A7,"Apples",B2:B7)

The result is 12 + 15 + 9, not the number of rows containing Apples. SUMIF adds the corresponding values in the sum range. The formula does not change the source data; it calculates a result in the cell where you enter it. If a new row is added below row 7, expand the ranges or use a table so the new record is included.

What the SUMIF arguments mean

SUMIF has three arguments: the range to check, the condition to apply, and the range of numbers to add. The third argument is optional. In the example, A2:A7 is the range Excel checks, “Apples” is the criterion, and B2:B7 is the sum range. The first and third ranges should normally cover the same number of rows and have the same shape so each checked record lines up with its amount.

Range: The cells Excel evaluates against the condition. This may contain text, numbers, or dates.
Criteria: The value or test a cell must meet. It can be typed into the formula or referenced from another cell.
Sum_range: The cells to add when the matching row in the range meets the condition. If omitted, Excel adds the cells in range itself.

Criteria written as text need double quotation marks in a formula. A cell reference used as a criterion is not enclosed in quotes. Excel’s function names and comma separators are shown in the standard Microsoft 365 for Windows format; regional settings may require semicolons instead of commas.

Build a formula using a cell criterion

Putting the condition in a worksheet cell makes a SUMIF easier to reuse. Enter Apples in D2, then refer to D2 in the formula. To test it, keep product names in A2:A7 and amounts in B2:B7, with Apples in D2; the formula returns 36:

=SUMIF(A2:A7,D2,B2:B7)

Change D2 to Oranges and the result updates to 19, because 8 + 11 = 19. This arrangement is useful for a report where someone may select or type a different product without editing the formula. To make the input easier to control, you can also use a dropdown list through Data > Data Validation in Excel for Windows. A dropdown is optional; SUMIF works with a manually entered criterion too.

Place the category or label to be checked in one column and the amounts to add in another.
Select the result cell and enter the function with the check range, criterion, and sum range in that order.
Press Enter, then compare the result with the matching source rows to confirm the ranges and condition are right.

Use numbers and comparison criteria

A criterion can test a number rather than match a label. For example, suppose B2:B7 contains the amounts 12, 8, 15, 6, 11, and 9. To add amounts greater than 10, the formula can use B2:B7 both as the range to check and, because no separate sum range is supplied, as the range to add. It returns 38: 12 + 15 + 11.

=SUMIF(B2:B7,">10")

Comparison operators such as greater than, less than, and equal to are included within the quoted criterion. For a threshold stored in another cell, join the operator text to the cell reference. With amounts in B2:B7 and a threshold of 10 in D2, this formula returns 38:

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

The ampersand joins the operator to the value in D2, creating a criterion Excel can evaluate. This is a common point of confusion: a comparison such as greater than 10 is not written as a bare expression inside the criteria argument. The comparison criterion needs to be supplied as text, or assembled from text and a cell reference.

Match partial text and dates

SUMIF can use wildcards when a text criterion should match only part of a cell. An asterisk represents any sequence of characters, including no characters; a question mark represents one character. With product names in A2:A7 and amounts in B2:B7, this formula matches names beginning with App and returns 36:

=SUMIF(A2:A7,"App*",B2:B7)

That can be useful when descriptions share a prefix, such as product codes or department names. To match a literal asterisk or question mark rather than use it as a wildcard, put a tilde before the character in the criterion. SUMIF text matching is not case-sensitive, so Apples and APPLES are treated as a match for the same text criterion.

Dates can be used as criteria too. Excel stores valid dates as serial numbers, so a date comparison can include a date typed in a cell. Suppose C2:C5 contains real Excel dates for March 4, March 12, March 3, and March 20, 2026, while D2:D5 contains 100, 80, 120, and 50. If F2 contains March 1, 2026, this formula adds the values for dates on or after F2 and returns 350:

=SUMIF(C2:C5,">="&F2,D2:D5)

All four dates are on or after March 1, so 100 + 80 + 120 + 50 = 350. If Excel treats a date as text rather than a date value, date comparisons may not behave as expected. Use actual date values in the source cells and check that the criterion cell also contains a valid date.

SUMIF or SUMIFS?

Use SUMIF when one condition is enough. Use SUMIFS when a total must meet two or more conditions, such as adding sales for Apples in the East region. The SUMIFS argument order differs: it starts with the sum range, followed by a criteria range and criterion pair for each condition.

With product names in A2:A7, sales in B2:B7, and regions in C2:C7, where the Apples rows have East, West, and East respectively, this formula returns 21:

=SUMIFS(B2:B7,A2:A7,"Apples",C2:C7,"East")

It adds the amounts for rows that meet both conditions: Apples and East. This is a frequent source of mistakes when moving between the functions: SUMIF puts its optional sum range last, while SUMIFS puts its sum range first. For a single condition, SUMIFS can also be used, but SUMIF is often simpler to read.

Common SUMIF problems and fixes

If a result is zero or unexpectedly small, check the criteria and ranges before changing the formula. A formula can be syntactically valid but still point to the wrong columns or omit data. The following checks resolve many ordinary issues:

Extra spaces or inconsistent labels: A cell containing Apples followed by an invisible space may not match the intended text. Check the source values, or clean imported data before relying on a total.
Numbers stored as text: Values that look numeric may be text and may not be added as expected. Confirm the cells are numbers, not text strings, particularly after copying data from another system.
Misaligned ranges: Make sure the criteria range and sum range refer to corresponding records. Using different-sized ranges can produce confusing outcomes; matching dimensions is the clearest practice.
Wrong quotation marks: Criteria need straight double quotation marks in a formula. Curly quotation marks copied from a formatted document can cause a formula error.
Dates stored as text: A displayed date is not necessarily a date value. Check the source and criterion cells if a date-based condition returns zero.
Range not extended: A formula that ends at row 100 will not include records entered in row 101. Expand the references or use an Excel table with structured references to include added records more naturally.

When investigating a result, temporarily filter the source list to the same category or condition and add the visible matching values independently. This provides a practical check on whether the formula is counting the expected rows and summing the intended column. Also inspect whether the workbook uses a different list separator: if Excel rejects a comma-separated formula, the regional configuration may expect semicolons.

Frequently Asked Questions

What is the basic SUMIF formula?
Use =SUMIF(range,criteria,sum_range) in concept: give Excel the cells to check, the condition, and the cells to add. The optional sum range can be left out when the checked cells are also the values to sum.

Can SUMIF use more than one condition?
No. SUMIF applies one condition. Use SUMIFS when a total must satisfy multiple conditions, such as a product name and a region at the same time.

Does SUMIF distinguish uppercase and lowercase?
No. Its text matching is not case-sensitive. If uppercase and lowercase distinctions matter, SUMIF alone is not the right case-sensitive test.

Why does my SUMIF return zero?
Check for a mismatched criterion, extra spaces, numbers or dates stored as text, and incorrect range references. Also verify that the matching records have numeric values in the sum range.

Can the SUMIF criterion come from a cell?
Yes. Put the value or label in a cell and use that cell reference as the criteria argument. For a comparison using a threshold cell, join the operator text to the reference with an ampersand.

Choosing a clear, maintainable formula

For a one-off total, a direct text criterion is concise. For a worksheet other people will use, placing the criterion in a clearly labeled cell makes the result easier to change and audit. Keep the criteria range and sum range adjacent when the worksheet layout allows, label the result, and make the source ranges cover the same records. If the list will keep growing, converting it to an Excel table can make formulas easier to maintain as rows are added.

The key idea is simple: SUMIF checks one range for one condition and adds the corresponding values from another range. Once you identify those two ranges and decide whether the criterion is fixed, entered in a cell, numeric, or date-based, the formula becomes much easier to build and troubleshoot.

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.