How to Use SUMIF in Google Sheets for Quick Totals

To total values that match one condition in Google Sheets, enter =SUMIF(A2:A7,"Travel",B2:B7) in an empty cell, assuming categories are in A2:A7 and amounts are in B2:B7. SUMIF checks each category for Travel and adds the amount from the matching row. Before you start, make sure the values you want to add are stored as numbers and that the condition range lines up with the sum range.

What You’ll Need

A Google Sheets spreadsheet open in a web browser.
A range containing the values or labels you want to test, such as product names or expense types.
A range containing the numbers to add. It should correspond row by row with the condition range.
An empty cell for the result, plus the criterion you want to apply, such as a category name or a threshold.

For the examples below, imagine a sheet with Category in column A and Amount in column B. Rows 2 through 7 contain Travel: 120, Food: 35, Travel: 80, Supplies: 45, Travel: 50, and Food: 25. The three Travel amounts add up to 250. You can use your own data instead; the important part is identifying the correct ranges.

Step-by-Step

Arrange and check your data. Put the condition values and amounts in separate, clearly labeled columns. Keep one record per row, and avoid blank rows inside the data if possible. For the example, categories are in A2:A7 and amounts are in B2:B7; row 1 contains headers and is not included. Check that the amounts are genuine numeric values, not numbers stored as text. Currency formatting is fine: formatting changes how a number looks, not the underlying value.
Choose where the total will appear. Click an empty cell outside the source data, such as D2, and type an optional label like Travel total in D1. Keeping the answer separate from the rows being summed makes it easier to check and reduces the chance that the formula will accidentally include its own result. Press Enter after entering the formula to calculate it.
Enter the basic SUMIF formula. With category labels in A2:A7 and amounts in B2:B7, enter =SUMIF(A2:A7,"Travel",B2:B7). The result is 250 because the formula finds Travel in rows 2, 4, and 6, then adds 120, 80, and 50. The general structure is SUMIF(range, criterion, [sum_range]): range is where Sheets looks for matches, criterion is what it looks for, and sum_range is what it adds. The square brackets in the description indicate an optional argument; do not type them into the formula.
Check the arguments from left to right. In the example, A2:A7 is the range to check, Travel is the criterion, and B2:B7 is the sum range. Notice that both ranges start and end on the same rows, so each category is paired with its amount. SUMIF does not add the label column when a separate sum_range is supplied. If you leave sum_range out, Sheets adds numbers in the condition range itself, which is useful when the values to test and add are in the same cells.
Use a cell for a changeable criterion. Instead of typing a category directly into the formula, enter Travel in D2. With categories in A2:A7, amounts in B2:B7, and the criterion in D2, enter =SUMIF(A2:A7,D2,B2:B7). It returns 250 while D2 contains Travel. Change D2 to Food and the total updates to 60, because 35 plus 25 equals 60. A cell reference is convenient for a dropdown or a summary table where you want to switch categories without editing the formula.
Apply a numeric condition when you need a threshold. SUMIF can check numbers as well as text. With amounts in B2:B7, enter =SUMIF(B2:B7,">50") to add the values greater than 50 in that same range. For the sample amounts, the result is 200: 120 plus 80. Put comparison operators and the number together in quotation marks for a fixed criterion. Common operators include greater than, less than, greater than or equal to, less than or equal to, and not equal to. The criterion is a condition, not a value that SUMIF automatically adds.
Confirm the result and copy carefully. Compare a small result with the matching source rows, as in the Travel calculation, before relying on it in a report. If you copy the formula to another cell, ordinary references can shift. For example, A2:A7 may become A3:A8 when moved down one row. If the source range must stay fixed as you copy, add dollar signs to its references, such as $A$2:$A$7 and $B$2:$B$7. Use fixed references only when that behavior is intended; a criterion-cell reference may need to move when filling a summary table.

Google Sheets recalculates the total when a source value or criterion changes. If you see a formula error immediately, check the separators used by your spreadsheet locale. The examples use commas; some locales require semicolons between arguments. Use the separator accepted by the sheet rather than changing the function name.

Common Mistakes

Putting arguments in the wrong order. SUMIF expects the condition range first, the criterion second, and the sum range third. Reversing the first and third ranges can make the formula search the amounts for a category name, or add the wrong column. Read the formula from left to right and identify each range before pressing Enter.

Using ranges that do not correspond. If the condition range covers rows 2 through 7 but the sum range starts at row 3, the values may no longer align with the records you intended. Keep both ranges the same shape and aligned to the same rows. Also exclude headers unless you deliberately want them included.

Typing text criteria without quotation marks. A typed text value such as Travel needs straight double quotation marks. If you refer to a cell containing the criterion, use the cell reference without putting it in quotation marks. Curly “smart quotes” copied from a document can also cause a parse error; replace them with standard keyboard quotes.

Assuming text matches are case-sensitive. SUMIF text matching is not case-sensitive, so Travel and TRAVEL are treated as the same criterion. However, extra spaces can make entries look alike while preventing the expected match. Check for leading or trailing spaces in the source labels and criterion cell.

Mixing text and numbers. A cell that looks like 120 may be stored as text, so it may not contribute as expected to a sum. Likewise, labels that differ slightly, such as Travel and Travel expenses, are not exact matches unless you use a wildcard. Keep the source data consistent and verify a few cells when a total seems too low.

Tips

Use a cell reference for a criterion that you expect to change, especially if you are building a compact summary. You can label the criterion cell so another person can see what the total represents. For a criterion that should remain constant, a text value in quotation marks is straightforward and easy to read.

SUMIF supports comparison criteria and wildcard matching. An asterisk represents any number of characters, while a question mark represents one character. For example, a criterion with an asterisk around a word can match text containing that word, rather than requiring the entire cell to equal it. Wildcards apply to text matching; they are not a substitute for numeric comparisons. If a label itself contains a wildcard character, a tilde can be used to treat that character literally.

When you need to combine conditions, use SUMIFS rather than stacking several SUMIF formulas without a plan. SUMIF handles one criterion; SUMIFS is designed to add values that meet multiple conditions, such as a category and a date range. For date criteria, make sure the source cells contain real date values, not date-looking text. Consistent data entry makes either function easier to audit.

Keep a quick manual check for important totals. Filter or scan the relevant rows and add a few values independently, particularly after importing data or changing the ranges. Use descriptive labels next to totals and, if the sheet is shared, make the criterion cell obvious. These small habits make it easier to spot an unexpected result before it flows into another calculation.

Troubleshooting

The result is zero. First confirm that the criterion exactly matches at least one cell, allowing for differences in spelling and extra spaces. Check that the condition range points to the label column, not the amounts column. If you use a criterion cell, verify that it is not blank or pointing to the wrong location. Then inspect the sum range and make sure matching rows contain numeric values.

The result is too high or too low. Compare the matching rows with the sum range one by one. A range that starts on the wrong row, includes old data, or stops before the last record can change the total. Check for duplicate records if they should not be counted twice. If the formula is copied across or down, inspect whether its ranges shifted; use dollar signs to lock ranges that should stay put.

Sheets shows a formula parse error. Check that the function name is spelled SUMIF, every opening parenthesis has a closing parenthesis, and text criteria use straight quotation marks. Confirm whether your locale expects commas or semicolons between arguments. The formula bar can help you inspect the exact characters rather than relying on how the cell displays the result.

Numbers seem to be ignored. Select a few amount cells and check whether they are numeric or text. If they were imported, pasted with symbols, or preceded by an apostrophe, Sheets may not treat them as numbers. Clean or convert the source values, then recalculate. Also make sure the values you want to add are actually in the specified sum range.

A wildcard or comparison does not behave as expected. For text, check that the wildcard is in the criterion and that the pattern describes the label you want to match. For numeric comparisons, put the operator and a fixed threshold together as text in quotation marks. If the threshold comes from a cell, join the operator to the cell reference rather than treating the reference as part of a quoted text string.

Frequently Asked Questions

What is the difference between SUMIF and SUM? SUM adds numbers in the range you specify without checking a condition. SUMIF first checks one condition and adds only the corresponding values that meet it. Use SUM for a plain total and SUMIF when the total depends on a category, threshold, or matching text.

Can SUMIF check more than one condition? SUMIF is for one criterion. For multiple requirements, such as matching a category and a region at the same time, use SUMIFS, which accepts a sum range followed by pairs of condition ranges and criteria. Check the function’s argument order when switching, because it differs from SUMIF.

Can the criterion be a date? Yes. Dates in Google Sheets are stored as values, so SUMIF can match a date or compare dates using criteria. Make sure the criterion and source cells are genuine dates and that you use the date format and comparison the task requires. For a period with a start and end date, SUMIFS is usually more suitable because it can apply both bounds.

Does SUMIF distinguish uppercase and lowercase? No. A text criterion is not case-sensitive, so entries with the same letters but different capitalization match. If capitalization is important, SUMIF alone is not the right case-sensitive test; consider a different approach rather than expecting the formula to separate those labels.

Why can I not see the full formula in the cell? The cell normally displays the calculated result, not the formula text. Select the cell to see its formula in the formula bar above the sheet. You can edit it there or in the cell itself. If the cell displays the formula instead of calculating it, check whether it was entered as plain text or preceded by an apostrophe.

Can I use SUMIF with data on another sheet? Yes. Include the sheet name in the range reference, and use an exclamation mark between the sheet name and the cell range. If the sheet name contains spaces, enclose the name in single quotation marks. Select the source range while editing the formula if you are unsure of the exact reference, then check that the condition and sum ranges still correspond.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.