SUMIF in Excel: Step-by-Step Formula, Examples, and Tips

To total values that match one condition in Excel, use SUMIF. If categories are in A2:A7 and amounts are in B2:B7, enter =SUMIF(A2:A7,"Apples",B2:B7) in an empty cell; with the example data below, it returns 36.

Set up a simple SUMIF example

SUMIF adds numbers associated with a particular label, number, date, or other condition. It is useful for questions such as “How much did we sell of one product?” or “What are the expenses for one department?” The condition is applied to one range, while Excel adds the corresponding values from a second range.

Start with this small worksheet. The first column contains categories, and the second contains amounts:

A1: Category; B1: Amount
Row 2: Apples; 12
Row 3: Oranges; 8
Row 4: Apples; 15
Row 5: Pears; 10
Row 6: Apples; 9
Row 7: Oranges; 11

The three amounts beside Apples are 12, 15, and 9, so their total is 36. The row numbers matter: the formula should use the data rows, not the header row. Including a text heading in the criteria range usually does not break this example, but selecting matching data ranges makes the formula easier to check and copy.

Enter the formula step by step

Click an empty cell where you want the total to appear, such as D2.
Type an equals sign and the function name, then open a parenthesis.
Select the cells Excel should check for the condition: A2:A7.
Type a comma, then enter the condition in quotation marks because Apples is text.
Type another comma and select the values to add: B2:B7.
Close the parenthesis and press Enter.

For this worksheet, with category labels in A2:A7 and amounts in B2:B7, the completed formula is =SUMIF(A2:A7,"Apples",B2:B7). Excel checks each category, adds the amount on rows where the category is Apples, and returns 36. On Windows, Enter confirms the formula; on a Mac, Enter or Return confirms it, depending on the keyboard and Excel settings.

You can also build a formula by selecting its ranges with the mouse while typing, but typing the references directly is often quicker for a small example. If Excel displays the formula as text instead of calculating it, check that the cell is not formatted as Text and that the formula begins with an equals sign. Re-entering the formula after changing the format to General can resolve that issue.

How SUMIF works

SUMIF uses three arguments: the range to test, the criterion to apply, and the range containing the numbers to add. The third argument is optional. When you provide it, the criteria range and sum range should cover corresponding rows and normally have the same dimensions. Excel tests the first cell in the criteria range and, when it matches, adds the value in the same relative position in the sum range. It continues this process for the rest of the range.

In the example, the first argument is A2:A7, the criterion is Apples, and the sum range is B2:B7. Excel finds matches in rows 2, 4, and 6, then adds B2, B4, and B6. This “same row” relationship is the key to understanding the result. If you accidentally pair category cells from one set of rows with amounts from a different set, the formula may return a number but answer the wrong question.

The sum range can be omitted when the cells being checked are themselves the numbers to total. For example, a list of quantities can be tested and summed from that same list. For category-and-amount data, however, specify the amount range so Excel knows which numbers belong to each matching label.

Use text, cell references, and comparison criteria

A criterion can be typed directly into the formula or taken from a worksheet cell. A cell reference is convenient when you want to change the category without editing the formula. With category labels in A2:A7, amounts in B2:B7, and the word Apples in D2, enter =SUMIF(A2:A7,D2,B2:B7) in another cell; it returns 36. Because D2 contains the criterion, the formula uses the reference without quotation marks around it.

For a numeric comparison, put the comparison operator and number together in quotation marks. With the amounts in B2:B7 and no separate sum range, =SUMIF(B2:B7,">10") returns 27: it adds 12 and 15, the only values greater than 10. A condition of greater than or equal to 10 would also include 10, so be precise about whether the boundary should count.

Text criteria in direct formulas need quotation marks. Criteria entered through a cell reference do not need quotation marks in the formula; put the desired criterion in the referenced cell instead. Excel’s text matching with SUMIF is generally not case-sensitive, so Apples and APPLES are treated as a match. If case must determine whether a value qualifies, SUMIF alone is not a case-sensitive solution.

Use wildcards for partial text matches

SUMIF supports wildcards in text criteria. An asterisk represents any number of characters, including no characters, while a question mark represents one character. This helps when a cell contains extra words or codes around the text you want to find. For example, if the category range is A2:A7 and amounts are in B2:B7, =SUMIF(A2:A7,"*apple*",B2:B7) returns 36 for the sample data. The asterisks allow text before or after apple; the match is not case-sensitive.

Wildcards are most useful for broad text matching, such as finding product descriptions that contain a word. They can also match more than you intended, so inspect the labels before relying on the total. If you need to find a literal asterisk or question mark rather than use it as a wildcard, place a tilde before that character in the criterion. For example, a tilde changes the wildcard meaning so the character is treated literally.

Common SUMIF problems and how to fix them

The answer is zero. Check spelling, spaces, and the selected ranges. A category that looks identical may contain a leading or trailing space. Also verify that the sum range contains numeric values rather than numbers stored as text.
The answer is too high or too low. Confirm that the criteria and sum ranges start and end on corresponding rows. Check whether the criterion is broad, such as a wildcard, and therefore matches additional labels.
The formula shows as text. Confirm that the cell is not formatted as Text, remove any leading apostrophe, and enter the formula again after changing the format if needed.
The comparison does not work as expected. Put an operator and its value together in a text criterion, and check the boundary: greater than 10 does not include 10.
The copied formula shifts unexpectedly. Relative references change when a formula is copied. If you need a range to stay fixed, select the reference in the formula bar and press F4 on Windows to cycle through absolute and mixed references. On Mac, the equivalent shortcut can depend on the keyboard; use the Excel shortcut shown for switching reference types or add dollar signs to the range manually.

One subtle issue is mismatched range lengths. If the criteria range and sum range are different sizes, Excel may still evaluate the formula, but the result can be confusing because the corresponding positions are no longer obvious. Keep both ranges aligned and use the same row boundaries. For a growing list, you can use full-column references, though a clearly bounded data range or an Excel Table is often easier to audit.

When to use SUMIFS instead

SUMIF handles one condition. Use SUMIFS when a total must satisfy two or more conditions, such as a particular product in a particular region. SUMIFS puts the range to add first, followed by pairs of criteria ranges and criteria. This argument order differs from SUMIF, which begins with the criteria range.

For example, imagine A2:A7 contains region names, B2:B7 contains product names, and C2:C7 contains sales amounts. Suppose the six rows are East/Apples/100, West/Apples/80, East/Pears/60, East/Apples/40, West/Pears/50, and East/Apples/30. With those columns and rows, =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"Apples") returns 170: it adds 100, 40, and 30, the East-region Apple sales. Use SUMIF for a single condition and SUMIFS when every specified condition must be met.

Excel version and platform notes

SUMIF is available in Microsoft Excel for Microsoft 365 on Windows, and the same function and formula structure are available in Excel for Mac and Excel for the web. The example does not depend on a newer dynamic-array function. You can enter it in a cell in any of these versions; the usual steps and result are the same.

The examples use commas between arguments, as required for the formula syntax shown here. Depending on regional settings, an Excel installation may expect semicolons as separators instead. If Excel rejects a correctly structured formula, check the list separator used by your installation. The quoted text criterion and the range logic remain the same.

Frequently Asked Questions

What is the difference between SUM and SUMIF?
SUM adds numbers in the ranges or cells you specify without checking a condition. SUMIF first checks whether cells meet one criterion, then adds the corresponding values. Choose SUM when every included number should count, and SUMIF when only matching rows should contribute.

Can SUMIF add values based on a date?
Yes. Dates in Excel are stored as serial numbers, so a date range can be used as the criteria range with a date condition. For dependable results, use a cell containing a real Excel date or construct the comparison using a date function, rather than relying on ambiguous date text. Make sure the values being tested are actual dates and not text that only looks like dates.

Why does SUMIF return zero when the labels look the same?
Possible causes include extra spaces, slightly different spelling, a criterion that refers to the wrong cell, or amounts stored as text. Check the actual cell contents, confirm the criteria and sum ranges line up, and test whether the amount cells are numeric. SUMIF does not distinguish uppercase from lowercase, so letter case alone is usually not the cause.

Can SUMIF use more than one condition?
Not in a single SUMIF condition. For multiple conditions that all need to be true, use SUMIFS. If the conditions describe alternatives rather than requirements that must all match, you may need to add separate SUMIF results or use another approach suited to the layout.

Should I include the header row in the ranges?
It is clearer to start at the first data row and stop at the last data row, as in A2:A7 and B2:B7. A text header normally will not be added as a number, but including headers can obscure range alignment and make a formula harder to review. Keep the compared and summed ranges aligned from row to row.

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.