How to Use SUMIF in Excel or Google Sheets

SUMIF adds numbers that meet one condition, such as totaling sales for one product or expenses in one category. You enter the range to check, the condition to match, and the numbers to add; the result updates when the source data changes. The steps below work in both Excel and Google Sheets.

What You’ll Need

• A spreadsheet containing the values you want to check and, usually, a corresponding column of numbers to total.
• A cell where you can enter the formula, plus a clear idea of the condition to apply.

Step-by-Step

1. Arrange your data in matching rows. Put the condition values in one column and the numbers to add in another. For example, list product names in A2:A6 and sales amounts in B2:B6. Each product and its sales amount should be on the same row. Avoid merged cells and keep headings outside the ranges you plan to use.
2. Select the result cell. Click a blank cell where you want the total to appear. Do not place it inside the data range if that could cause the formula to include its own result.
3. Start the function. Type =SUMIF(. The standard syntax is =SUMIF(range, criteria, [sum_range]). The square brackets indicate that sum_range is optional; do not type the brackets into your formula.
4. Choose the range to test. Select or type the cells containing the values that should be checked. In the example, this is A2:A6.
5. Enter the condition. Type a comma, then provide the criterion. To match a text value, put it in quotation marks: "Apples". For the example, enter "Apples" to include rows labeled Apples.
6. Choose what to add. Type another comma and select the numbers to total, such as B2:B6. Close the parenthesis. The complete example is =SUMIF(A2:A6,"Apples",B2:B6). Press Enter or Return to calculate the sum.
7. Check the result. Find the rows whose labels are Apples and add their values in column B. The formula’s result should match that manual total. If the data grows, update the ranges or use a range that includes the expected additional rows.
8. Use a cell as the condition when it may change. If D2 contains Apples, enter =SUMIF(A2:A6,D2,B2:B6). You can replace D2 with another label without editing the formula. For a condition such as greater than 100, use =SUMIF(B2:B6,">100"); because the tested and summed range is the same, the third argument is unnecessary.

Common Mistakes

• Mixing up the ranges. The first range is checked against the condition; the optional third range supplies the values to add. Reversing them can produce an incorrect or unexpected result.
• Using ranges of different sizes. Make the checked range and sum range cover corresponding rows, for example A2:A100 and B2:B100. If they do not align, results may be misleading, and spreadsheet behavior can vary.
• Leaving out quotation marks around text. Type "Apples" when the criterion is written directly in the formula. A cell reference such as D2 does not need quotes.
• Typing a comparison operator incorrectly. For a numeric condition, put the operator and number together inside quotes, as in ">=50". A criterion written as >=50 without quotes is generally not interpreted as intended.
• Assuming text must match exactly in every detail. SUMIF is not case-sensitive, so Apples and APPLES match. Extra spaces can still prevent a match; a cell containing “Apples ” may not behave like one containing “Apples”.
• Including headings or totals accidentally. Start the ranges at the first data row and check that existing subtotals are not being added a second time.

Tips

• Use absolute references, such as $A$2:$A$100, when copying a formula and keeping its data ranges fixed. Select a reference in the formula and press F4 in many desktop spreadsheet setups to cycle through reference styles; keyboard behavior can vary.
• For a criterion containing a comparison operator, build it from a cell with concatenation. If D2 contains a threshold, use =SUMIF(B2:B100,">"&D2). The ampersand joins the operator to the cell value.
• Use wildcard characters for partial text matches: * represents any number of characters, and ? represents one character. For example, "*berry*" matches text containing “berry”. To match a literal asterisk or question mark, precede it with a tilde.
• For several independent conditions, use SUMIFS instead. Its argument order differs: it starts with the sum range, followed by condition ranges and criteria.
• Format the result cell as a number, currency, or another suitable display format if needed. Formatting changes how a value looks, not the underlying sum.

Troubleshooting

The result is zero. Confirm that the criterion actually appears in the checked range and that there are no extra spaces or spelling differences. If the criterion is in a cell, inspect its contents. For a comparison, make sure the operator and value are joined correctly and that the relevant values are numeric rather than numbers stored as text.

The total is too high or too low. Check which rows match, whether the sum range starts and ends on the same rows as the checked range, and whether headings or prior totals are included. Also check that the numbers to add are stored as numbers. Text that looks like a number may not be included as expected.

The formula appears as text or shows an error. Make sure the entry begins with =, the parentheses are balanced, and the range references are valid. Some spreadsheet locales use semicolons instead of commas between arguments; if a correctly written comma-based formula is rejected, try semicolons. If a cell displays the formula literally, change its format to a general or automatic format, then re-enter the formula.

New rows are not counted. The formula only checks the range it names. Extend both ranges to cover the new rows, or use an appropriately broad range such as A:A and B:B. Avoid broad ranges if they include unrelated data or make a large workbook slower.

Frequently Asked Questions

What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition. SUMIFS adds values only when multiple conditions are met, such as a matching product and a date within a specified period.

Can SUMIF add values when the condition is in the same range?
Yes. Leave out the optional sum range, as in =SUMIF(B2:B20,">100"). The function checks and adds values in B2:B20.

Does SUMIF distinguish uppercase and lowercase text?
No. It is not case-sensitive. Use a different approach if capitalization must determine which rows qualify.

Can I use a cell reference for the criterion?
Yes. Put the desired criterion in a cell and refer to it, such as =SUMIF(A2:A20,D2,B2:B20). For operators based on a cell value, join the operator and reference with &.

Why does SUMIF return zero when matching text looks identical?
Look for leading or trailing spaces, hidden characters, or differences in the actual cell contents. You can clean source text with spreadsheet tools or functions such as TRIM, then check the formula again.

Related

• Reconcile Inter Branch Transfers & GST Entries in Indian Bank Accounts
• AI Wildfire Detection: Cameras, Satellites & Sensors
• Smartphones Dropping Headphone Ports: Why and What to Do