SUMIF: sum with conditions
Sum only values that meet a condition. Perfect for sales, budgets and filtered analyses.
SUMIF is the intelligent version of SUM. It allows you to sum only numbers that meet a specific condition, automatically filtering out the others.
How SUMIF works
Syntax:
- range: where to check the condition
- criteria: the condition to be met
- sum_range: values to sum (optional, uses range if omitted)
Example 1: Sum sales over $1000
You have sales in column B and want to sum only those over $1000.
Formula:
Excel sums only cells in B2:B100 that contain values greater than 1000.
Example 2: Sum sales by salesperson
Column A has salesperson names, column B has amounts. You want the total for "John Smith".
Formula:
Search for "John Smith" in column A, and sum the corresponding values in column B.
Dynamic version (name in a cell):
If the salesperson name is in E2, you can change it without modifying the formula
Example 3: Sum expenses by category
Column C has expense categories, column D has amounts. You want the total for "Marketing".
Formula:
Advanced criteria
Greater than (>):
Less than (<):
Not equal to (<>):
Sum everything except rows with "Cancelled"
Contains text (with *):
Sum all rows starting with "New York"
Equal to a cell:
The criteria is the value written in E2
Greater than a cell:
Sum values greater than the number in F2
SUMIFS: multiple conditions simultaneously
If you need to apply multiple conditions (e.g., sales > $1000 AND category "Premium"), use SUMIFS.
Syntax:
Example: sales > $1000 by salesperson "John"
Sum values in C that meet BOTH conditions
Common errors
Zero result (but there are values)
Problem: The criteria is written wrong or the ranges don't match
Solutions:
- Check that numeric criteria are in quotes: ">100"
- Check that range and sum_range have the same length
- Check uppercase/lowercase and spaces in text criteria
Criteria with cell reference doesn't work
Problem: You write =SUMIF(A:A, > B1) instead of =SUMIF(A:A, ">"&B1)
Solution: Use quotes and concatenate: =SUMIF(A:A, ">"&B1)
Practical tips
1. Use absolute references for tables
The $ symbols lock the table when you copy the formula, but E2 adapts to E3, E4, etc.
2. Combine with other formulas
Sum only values from the current month
Tired of building complex criteria?
With AutoExel you describe what you want to filter and AI creates the correct SUMIF formula. No syntax to remember.
Try AutoExel FreeFrequently asked questions
Conditional sums without effort
AutoExel creates filters and sums automatically from your data. Forget formulas, focus on results.