SUMIF with One Condition in Excel and Google Sheets
Purpose
SUMIF was the first formula that made me feel like I actually understood Excel. Before it, I would filter a…
SUMIF was the first formula that made me feel like I actually understood Excel. Before it, I would filter a column, manually note the total, then undo the filter. SUMIF replaced all of that — point it at a condition and it gives you the total in one step, with no filtering needed. Total sales for the East region. Total hours logged this week. Total spend on a specific vendor. All of these are single-condition sums and SUMIF handles them all without touching the layout of your data. The formula has three parts: the column you are testing for the condition, the condition itself, and the column you want to add up. Once those three click, you will find yourself using SUMIF constantly. It has been in Excel since version 2003 and works identically in Google Sheets, making it one of the most portable and reliable formulas in your toolkit.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=SUMIF(range, criteria, [sum_range])
Arguments
| Argument | Required | Description |
|---|---|---|
| range | Required | The column or range to check the condition against. SUMIF evaluates each cell in this range against the criteria argument. Typically a column of categories, department names, dates, or region codes. |
| criteria | Required | The condition to match. Text must be in quotes: "Sales". Numbers can be bare: 100. Comparisons use operators: ">500", "<=0", "<>0". Wildcards: "Sales*" matches anything starting with Sales. Cell references: A1 uses whatever value is in A1. Operator plus reference: ">"&A1. |
| sum_range | Optional | The column containing the values to add. Must be the same height as range. If omitted, SUMIF sums the range itself. |
How it works
SUMIF scans through the range argument row by row, checking whether each cell meets the criteria. When it finds a match, it adds the corresponding cell from sum_range to a running total. The range and sum_range do not have to be the same column, but they must be the same size — the same number of rows — so corresponding rows line up correctly. If you omit the sum_range argument, SUMIF sums the values in the range argument directly, which is useful when you want to sum values in a column that also contains the condition. Criteria can be a specific value in quotes, a number, a comparison like ">500", a wildcard pattern like "Sales*", or a cell reference. When using comparisons or wildcards with a cell reference, concatenate them: ">"&A1 means greater than whatever is in A1.
Examples
1
Sum the total sales for the Sales department from a list of employee records.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Department | Sales |
| 2 | Alice | Sales | 12500 |
| 3 | Bob | Marketing | 0 |
| 4 | Carol | Sales | 9800 |
| 5 | David | Engineering | 0 |
| 6 | Eve | Sales | 15200 |
| 7 | Frank | Marketing | 0 |
| 8 | Grace | Sales | 7100 |
| 9 | Hank | HR | 0 |
| 10 | Iris | Sales | 11400 |
| 11 | James | Engineering | 0 |
Row 2: 56000
— Alice (12500) + Carol (9800) + Eve (15200) + Grace (7100) + Iris (11400) = 56000 total for the Sales department.
SUMIF ignores Marketing, Engineering, and HR rows entirely. Only the five Sales rows contribute to the total. The formula does not care about row order — it checks every row.
2
Sum all expenses greater than 500 from an expense log.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Date | Category | Amount | Total >500 |
| 2 | 2024-01-05 | Travel | 850 | =SUMIF(C2:C11,">500",C2:C11) |
| 3 | 2024-01-08 | Software | 299 | |
| 4 | 2024-01-12 | Travel | 1200 | |
| 5 | 2024-01-15 | Office | 75 | |
| 6 | 2024-01-18 | Travel | 620 | |
| 7 | 2024-01-22 | Software | 450 | |
| 8 | 2024-01-25 | Training | 980 | |
| 9 | 2024-01-28 | Office | 45 | |
| 10 | 2024-01-30 | Travel | 1100 | |
| 11 | 2024-01-31 | Software | 520 |
Row 2: 5270
— Travel 850, Travel 1200, Travel 620, Training 980, Travel 1100, Software 520 = 5270. Items below 500 are excluded.
When sum_range is the same as range, SUMIF sums the values in the column that meet the condition. This is a compact way to sum values above or below a threshold.
Common use cases
1. Total revenue from a specific region in a sales report
=SUMIF(A:A,"North",C:C)
2. Sum all expenses for a particular vendor from an accounts spreadsheet
=SUMIF(B:B,"Office Depot",D:D)
3. Add up all hours logged by a specific employee in a timesheet
=SUMIF(C:C,"Alice",D:D)
4. Sum all transactions above a value threshold
=SUMIF(B:B,">1000",B:B)
5. Total spend this year only, matching a partial category name
=SUMIF(A:A,"Marketing*",C:C)
Common errors
Returns zero when rows clearly match
The condition text does not exactly match the cell content — usually due to extra spaces, case differences in text, or a data type mismatch where numbers are stored as text.
Fix: Use TRIM on the range column to remove spaces. Check that number comparisons use numbers not text: use >500 not ">500" when comparing to a number in a cell.
Wrong total — includes too many or too few rows
The criteria argument uses = when it should use a wildcard, or the range and sum_range are different sizes and misaligned.
Fix: Check that range and sum_range cover exactly the same rows. If you want partial matching use asterisks: "Sales*" instead of "Sales".
#VALUE!
The range or sum_range contains errors such as #DIV/0! and SUMIF cannot handle them.
Fix: Wrap individual cells in IFERROR or use SUMIF with IFERROR around the whole formula.
Tips and variations
Reference a cell for the criteria to make a dynamic summary table
Instead of hardcoding the department name, reference a cell: =SUMIF(B:B,E2,C:C). Then put department names in column E and the formula adjusts automatically. This is the foundation of a simple pivot-style summary without using an actual PivotTable.
=SUMIF(B:B,E2,C:C)
Use wildcards for partial category matching
When categories have variations — Sales North, Sales East, Sales West — use a wildcard to sum all of them together. An asterisk at the end matches any trailing characters. Put the wildcard in quotes as part of the criteria string.
=SUMIF(A:A,"Sales*",C:C)
Use full column references to handle new data automatically
Using B:B instead of B2:B100 means the formula automatically includes new rows added below the current data. The overhead of scanning empty cells is negligible in modern Excel and Google Sheets.
=SUMIF(B:B,"Sales",C:C)
Excel vs Google Sheets
Excel vs Google Sheets
SUMIF works identically in Excel and Google Sheets. Same three arguments, same criteria syntax, same wildcard support. Formulas copy between applications without any changes. Both applications support full column references like B:B as the range argument.
Frequently asked questions
SUM adds every value in a range with no filtering. SUMIF adds only the values where a corresponding cell in another column meets a condition. Use SUM when you want the total of everything. Use SUMIF when you want the total for a specific category, department, region, or any other single condition.
Yes — if the condition range and the sum range are the same column, SUMIF sums only the values that meet the condition in that same column. For example =SUMIF(A:A,">100",A:A) adds up all values in column A that are greater than 100.
Use an asterisk * as a wildcard in the criteria. =SUMIF(A:A,"Sales*",B:B) sums column B wherever column A starts with Sales — matching Sales, Sales Manager, Salesperson, and so on. A question mark ? matches exactly one character.
SUMIF handles one condition. SUMIFS handles multiple conditions simultaneously using AND logic — every condition must be true for a row to be summed. SUMIFS also puts the sum range first instead of last, which is a syntax difference to watch out for. If you only have one condition, either works but SUMIF is slightly simpler to write.
Yes — use standard sheet reference syntax: =SUMIF(Sheet2!A:A,"Sales",Sheet2!B:B). All three arguments can reference other sheets. You can even mix sheets — look up the condition in one sheet and sum from another, though keeping data on one sheet is usually cleaner.