If you want to add up only the cells that meet a condition, the SUMIF function is the quickest way to do it. You give it a range to check, a condition to look for, and the cells you want to total.
This tutorial walks through the syntax and six hands-on examples. SUMIF returns a single total, but in Excel 365 you can also get the same result inside a dynamic array formula like =SUM(FILTER(…)).
SUMIF Function Syntax in Excel
Here is what the SUMIF function looks like:
=SUMIF(range, criteria, [sum_range])
- range = the cells checked against the criteria.
- criteria = the condition (a number, text in quotes, an expression like “>100”, or a cell reference).
- sum_range (optional) = the cells actually added up.
If you leave out sum_range, SUMIF adds the cells in range itself.
When to Use the SUMIF Function
- You want to total sales for one person, product, or region.
- You need to add up only values above or below a number.
- You want to sum a group of items using a wildcard like “Laptop*”.
- You need to add everything except one category.
- You want a quick one-condition total without filtering or sorting.
Example 1: Sum Sales for One Person
Let us start with the most common case, totaling sales for a single rep.
The data has Sales Rep names in column A and Sales in column B, across rows 2 to 11.
You want to add up all the sales made by John Carter.
Here is the formula:
=SUMIF(A2:A11,"John Carter",B2:B11)
This returns 9100, which is John Carter’s three sales added together.
This is the three-argument form. The first range is what you check (the names), and the third range is what you add (the sales). They sit in different columns, and SUMIF lines them up row by row.
Example 2: Sum Values Above a Number
Sometimes you do not have a name to match, just a number cutoff.
Here the data is a single column, Order Amount, in rows 2 to 9, with values from 45 to 500.
You want to add up every order amount over 100.
Here is the formula:
=SUMIF(A2:A9,">100")
This returns 1130, the total of every order above 100.
The cells you check are the same cells you add, so you can leave out sum_range entirely. Notice the operator and number go inside quotes as “>100”.
Example 3: Sum a Whole Product Family
Wildcards let you total a group of related items without listing each one.
The data has Product names in column A and Revenue in column B, across rows 2 to 9.
You want to add up the revenue from every product whose name starts with “Laptop”.
Here is the formula:
=SUMIF(A2:A9,"Laptop*",B2:B9)
This returns 4250, the revenue from all the Laptop products combined.
The * matches any number of characters, so “Laptop*” sums anything that begins with Laptop. If you only need to match a single character, use ? instead.
Example 4: Sum Above a Threshold From a Cell
Often the cutoff lives in its own cell so you can change it easily.
Here the data is a single column, Expense, in rows 2 to 9, with a Threshold value of 2000 sitting in cell C2.
You want to add up every expense above the threshold stored in C2.
Here is the formula:
=SUMIF(A2:A9,">"&C2)
This returns 12000, the total of every expense above 2000.
You join the operator (in quotes) to the cell with &. Change the value in C2 and the total updates on its own. The sum_range is omitted because you are summing the same column you are testing.
Pro Tip: The operator stays inside the quotes and the cell reference goes outside, joined with &. Writing “>C2” inside quotes would be treated as literal text and match nothing.
Example 5: Sum Everything Except One Category
You can also flip the logic and add everything that does not match.
The data has Region names in column A and Sales in column B, across rows 2 to 9.
You want to add up the sales from every region except North.
Here is the formula:
=SUMIF(A2:A9,"<>North",B2:B9)
This returns 9600, the total of South, East, and West combined.
The <> symbol means “not equal to”. It is handy for excluding one category without having to list all the others.
Example 6: Conditional Sum the Modern Way
If you are on Excel 365, there is a fresh way to do a conditional sum.
The data has Sales Rep names in column A and Sales in column B, across rows 2 to 11, same shape as Example 1.
You want to add up Priya Nair’s sales using a single dynamic array formula instead of SUMIF.
Here is the formula:
=SUM(FILTER(B2:B11,A2:A11="Priya Nair"))
This returns 10400. FILTER pulls out just Priya Nair’s sales as a list, and SUM adds them up. You get the same answer as =SUMIF(A2:A11,"Priya Nair",B2:B11).
The bonus is that FILTER can do OR logic, which SUMIF cannot. You add a second condition with + to total rows that match either one. This needs Excel 365.
Tips and Common Mistakes
- For more than one condition, use SUMIFS instead of SUMIF.
- For OR logic, or if you are on Excel 365, reach for SUM(FILTER(…)).
- Keep sum_range the same size and shape as range, or the result can be off.
- Wrap text and operators in quotes, and join cell references with
&. - SUMIF is not case-sensitive, so “north” and “North” are treated the same.
SUMIF earns its keep the moment you need a quick conditional total. Start with the three-argument form, then add wildcards, operators, and cell references as you need them. When one condition is not enough, SUMIFS and SUM(FILTER(…)) pick up where it leaves off.
Related Excel Functions / Articles:
