If you need to add up values in a spreadsheet only when they meet a certain condition — for example, total sales for one product, or all expenses in a specific category — SUMIF is the function you need. It is one of the most useful formulas in Excel for anyone managing lists of data, and once you understand how the three arguments work, it becomes second nature.
What SUMIF Does
SUMIF looks at a column (or range of cells), checks each row against a condition you specify, and adds up the corresponding values in another column where that condition is met. For example: look at column A, find every row that says “Widgets”, and add up the numbers in column B for those rows only.
It does not matter how long your list is. SUMIF will scan every row and sum only the matching ones.
The Three Arguments
The SUMIF formula looks like this:
=SUMIF(range, criteria, sum_range)- range — the column you want to check (for example, A2:A100)
- criteria — the condition you are looking for (a value, text, or comparison)
- sum_range — the column containing the numbers to add up (for example, B2:B100)
A simple example: if column A contains product names and column B contains sales figures, this formula totals all sales for “Widgets”:
=SUMIF(A2:A100, "Widgets", B2:B100)Text Criteria vs Number Criteria
When matching text, wrap the value in double quotes: "Widgets". Text matching is not case-sensitive, so “widgets” and “WIDGETS” will both match.
When matching a specific number, you can type it directly without quotes:
=SUMIF(A2:A100, 5, B2:B100)This adds up values in column B wherever column A equals exactly 5.
Using Comparison Operators
You can use greater than, less than, and other operators inside the criteria. These must be written as text strings in double quotes:
=SUMIF(B2:B100, ">100", C2:C100)This sums values in column C where column B is greater than 100. Other operators you can use: <, >=, <=, <> (not equal to).
If your threshold is in a cell rather than typed directly, use the ampersand (&) to join the operator and the cell reference:
=SUMIF(B2:B100, ">="&D1, C2:C100)This sums column C where column B is greater than or equal to whatever value is in cell D1. This is much more flexible because you can change the threshold without editing the formula.
Wildcard Criteria
Wildcards let you match partial text. The asterisk (*) represents any number of characters:
=SUMIF(A2:A100, "North*", B2:B100)This matches “North”, “North East”, “Northampton”, or anything else beginning with “North”. A question mark (?) matches exactly one character, so “Jon?” would match “John” or “Jones” but not “Jonathan”.
Wildcards only work with text criteria, not numbers.
When sum_range Is the Same as range
If the column you are checking is also the column you want to sum, you can leave out the third argument entirely:
=SUMIF(B2:B100, ">0")This adds up all positive numbers in column B. Excel uses the range as both the check column and the sum column when sum_range is omitted.
SUMIFS for Multiple Criteria
SUMIF only handles one condition. When you need two or more conditions that must all be true, use SUMIFS instead. The argument order changes slightly:
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)The sum range comes first, then each range-and-criteria pair. For example, to total sales for “Widgets” in the “North” region:
=SUMIFS(C2:C100, A2:A100, "Widgets", B2:B100, "North")All conditions must be met for a row to be included. You can add as many criteria pairs as you need.
Practical Examples
Total Sales for a Specific Product
You have a sales log with product names in column A and sale amounts in column B. To see total revenue from one product:
=SUMIF(A2:A500, "Blue Widget", B2:B500)Or, if the product name is in cell E2 so you can change it without editing the formula:
=SUMIF(A2:A500, E2, B2:B500)Total Expenses in a Category
Your expenses sheet has categories in column B and amounts in column C. To total all “Travel” expenses:
=SUMIF(B2:B200, "Travel", C2:C200)To then break this down by month, add SUMIFS with a date column:
=SUMIFS(C2:C200, B2:B200, "Travel", A2:A200, ">="&DATE(2025,1,1), A2:A200, "<"&DATE(2025,2,1))Sum All Values Above Zero
A quick way to total only positive entries in a column (ignoring negatives or zeros):
=SUMIF(D2:D100, ">0")Common Errors
#VALUE! — Usually caused by a typo in the criteria, such as a misplaced quote or using a range for criteria that contains non-compatible data.
Wrong result (not zero, but not right) — Check that your ranges are the same size. If range is A2:A100 and sum_range is B2:B50, Excel will still run but may give unexpected results. Make them the same length.
Text matching fails — If your data has leading or trailing spaces, SUMIF will not match. Use TRIM to clean the data first, or use a wildcard: "*Widgets*".
Numbers stored as text — If numbers in your sum_range are actually stored as text (you will see a small green triangle in the cell corner), SUMIF will skip them. Select the column, click the warning triangle, and choose “Convert to Number”.
SUMIFS returns zero unexpectedly — Double-check that all criteria are correct. SUMIFS uses AND logic, so every single condition must be true. If any one fails for a row, that row is excluded.
