SUMIF Function in Excel and Google Sheets
Purpose
Add only the values in a range that correspond to cells meeting a single condition
Return value
A number — the sum of all values in sum_range where the corresponding cell in range matches criteria
SUMIF replaced my most tedious spreadsheet habit — filtering a column, noting the subtotal at the bottom, then removing the filter. I did this constantly before I learned SUMIF. SUMIF does exactly the same thing automatically, permanently, and without touching any filters. Point it at the column you want to test, give it the condition to match, point it at the column to add up. That is the entire function. Total sales for the North region. Total expenses for a specific vendor. Total hours logged by a particular employee. Total orders above a given threshold. All of these are SUMIF in one formula. Because SUMIF updates automatically whenever the data changes, formulas built on it stay accurate without any manual maintenance. It handles text conditions, numeric comparisons, date ranges, and wildcard patterns, and it works identically in Excel and Google Sheets making it one of the most portable and reliable formulas in any analyst's toolkit. Once you are comfortable with SUMIF, the step to SUMIFS for multi-condition totals is a short one.
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. Each cell in this range is tested against criteria. Typically a column of category names, region labels, or dates. |
| criteria | Required | The condition to match. Text in quotes: "Sales". Numbers: 100. Comparisons: ">500". Wildcards: "Sales*". Cell references: A1. Operator with reference: ">"&A1. |
| sum_range | Optional | The column containing the values to add. Must be the same height as range. If omitted, SUMIF sums the values in range itself. |
How it works
SUMIF scans through the range argument row by row, checking each cell against criteria. When it finds a match, it adds the corresponding cell from sum_range to a running total and then continues scanning. The range and sum_range do not need to be the same column — they just need to cover the same number of rows so that corresponding rows line up correctly. The criteria argument is flexible: text conditions must be in quotes, numeric conditions can be bare numbers or comparison strings like ">500", and wildcards work in text criteria using * and ?. When you need to compare against a value in another cell, concatenate the comparison operator with a reference: ">"&A1 means greater than whatever is in A1. Using full column references like B:B instead of B2:B100 means the formula automatically captures new rows added to the data without any formula updates. SUMIF only supports one condition — if you need two or more, use SUMIFS instead which extends the same logic to multiple criteria pairs.
Examples
1
Sum total sales for the Sales department only.
| 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
— 12500 + 9800 + 15200 + 7100 + 11400 = 56000. Only Sales rows are included.
SUMIF ignores Marketing, Engineering, and HR rows entirely. Row order does not matter — it checks every row in the range.
2
Sum all expenses greater than 500.
| A | B | C | |
|---|---|---|---|
| 1 | Date | Category | Amount |
| 2 | 2024-01-05 | Travel | 850 |
| 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 |
Row 2: 4750
— 850+1200+620+980+1100 = 4750. Items at or below 500 are excluded.
When sum_range is the same column as range, SUMIF sums values that meet the condition. This is the most compact way to total above or below a threshold.
3
Sum sales for all regions starting with "North" using a wildcard.
| A | B | |
|---|---|---|
| 1 | Region | Sales |
| 2 | North | 125000 |
| 3 | South | 98000 |
| 4 | North East | 45000 |
| 5 | West | 210000 |
| 6 | North West | 38000 |
| 7 | East | 87000 |
| 8 | North Central | 62000 |
| 9 | South East | 73000 |
Row 2: 270000
— North (125000) + North East (45000) + North West (38000) + North Central (62000) = 270000.
The asterisk * matches any characters after "North" so all four North variants are summed in one formula without listing each one separately.
Common use cases
1. Total revenue from a specific sales region by checking a region column and summing a revenue column
=SUMIF(A:A,"North",C:C)
2. Sum all expenses charged to a particular vendor by matching the vendor name column exactly
=SUMIF(B:B,"Office Depot",D:D)
3. Add up all hours logged by a specific employee across an entire timesheet
=SUMIF(C:C,"Alice",D:D)
4. Sum all transaction amounts that exceed a threshold value held in a reference cell for easy adjustment
=SUMIF(B:B,">"&E1,B:B)
5. Total spend for all expense categories that start with Marketing using a wildcard to catch all variants
=SUMIF(A:A,"Marketing*",C:C)
Common errors
Returns zero when matching rows clearly exist in the data
Almost always a data type mismatch. Numbers stored as text are left-aligned in cells and look identical to real numbers but cannot be matched by numeric criteria. This is the most common SUMIF bug and the hardest to spot by eye because the data looks correct on screen.
Fix: Check cell alignment — real numbers are right-aligned by default. Use VALUE to convert text-numbers to real numbers. If you cannot change the source data, use the criteria "="&A1 which matches the text representation, or SUMPRODUCT as an alternative.
Total is too high or too low but no error message appears
The range and sum_range arguments cover different numbers of rows so corresponding rows do not line up correctly, or a wildcard pattern is broader than intended and captures rows it should not.
Fix: Ensure range and sum_range cover exactly the same rows using the same start and end row numbers. Test your wildcard criteria with a COUNTIF first to see exactly which rows it matches before committing to SUMIF.
#VALUE! error
The criteria argument contains an array or the range contains error values that SUMIF cannot process. Also occurs when criteria contains an operator like > without a value to compare against.
Fix: Ensure criteria is a single value not an array. Wrap individual cells in IFERROR if the range contains error values from other formulas. Check that comparison criteria include both an operator and a value: ">500" not just ">".
Tips and variations
Use a cell reference for criteria to build a self-updating dynamic summary table
Instead of hardcoding the department or region name in the formula, reference a cell: =SUMIF(B:B,E2,C:C). Put category names in a column of cells and the formulas adjust automatically when you change the filter value. This is the foundation of dashboard-style pivot summaries without PivotTables and without any formula editing.
=SUMIF(B:B,E2,C:C)
Use wildcard criteria to sum across variations of a category name
When a category appears in multiple forms — Sales North, Sales East, Sales Team 1 — you cannot match all of them with an exact text condition. A wildcard like "Sales*" captures every variation starting with Sales in a single formula without listing each one separately.
=SUMIF(A:A,"Sales*",C:C)
Use full column references so the formula never needs updating as data grows
Writing B:B instead of B2:B100 means SUMIF automatically includes every row ever added to the spreadsheet. Modern Excel and Google Sheets handle the tiny overhead of scanning empty cells at the bottom of a column invisibly, and the benefit of never needing to expand the range manually is worth it in any regularly updated dataset.
=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 the two without any changes.
Frequently asked questions
SUM adds every value in a range without any filtering — every cell in the range is included in the total. SUMIF adds only the values where a corresponding cell in a separate column meets a condition you specify. Use SUM when you want the total of everything. Use SUMIF when you only want the total for a specific category, region, status, or any other single criterion. The SUMIF result changes automatically as the data changes.
Yes — SUMIF supports two wildcard characters in text criteria. The asterisk * matches any sequence of characters of any length, including none. The question mark ? matches exactly one character. =SUMIF(A:A,"Sales*",B:B) sums column B wherever column A starts with the word Sales, matching Sales, Sales Manager, Sales East, Salesperson, and any other value starting with Sales. Use SUMIF(A:A,"*sales*",B:B) to match the word sales anywhere in the cell.
SUMIF handles exactly one condition — one range, one criteria. SUMIFS handles two or more conditions simultaneously using AND logic, where every condition must match for a row to be included. SUMIFS also places sum_range as its first argument whereas SUMIF places it last — this is a common source of bugs when switching between them. If you only need one condition, SUMIF is slightly more concise to write. For two or more conditions, always use SUMIFS.
Yes — you can reference any sheet in any or all three arguments using the standard sheet reference syntax. =SUMIF(Sheet2!A:A,"Sales",Sheet2!B:B) checks column A of Sheet2 against the criteria and sums column B of Sheet2. You can even mix sheets across arguments — the range can be on one sheet while sum_range is on another.
The most common cause is a data type mismatch between the data in the criteria range and the value in the criteria argument. Numbers stored as text look identical to real numbers on screen because Excel and Google Sheets display them the same way, but they are fundamentally different types that SUMIF cannot match against each other. The telltale sign is left-aligned cells in a column that should contain numbers — left alignment indicates text. Use VALUE to convert text-numbers to real numbers, or check the source data import settings to ensure numbers import as numeric values.