SUMIFS adds up numbers that meet more than one condition. For example, total sales for one region in one month.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
The sum range comes first, which is the opposite order of SUMIF. Every criteria range must be the same size as the sum range.
Example with text criteria
Sales amounts are in C2:C100, region in A2:A100 and product in B2:B100:
=SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Laptop")
This adds the sales only where region is North and product is Laptop.
Using dates
To sum between two dates, join the operator and the date cell with &:
=SUMIFS(C2:C100, D2:D100, ">="&F1, D2:D100, "<="&G1)
Using numbers and operators
- Greater than 500:
">500" - Not equal to Pending:
"<>Pending" - Contains "Pro":
"*Pro*"
Common mistakes
- Ranges of different sizes give a #VALUE! error.
- Dates stored as text will not match. Convert them to real dates first.
- Forgetting the quotes around operators like
">500".
SUMIFS is not case sensitive, and it treats all conditions as AND. For OR logic, add two SUMIFS together.