SUMIF adds up the numbers that meet one condition, for example the total sales of one region.
Syntax
=SUMIF(range, criteria, [sum_range])
- range: the cells to test.
- criteria: the condition, such as
"North"or">500". - sum_range: the numbers to add. If left out, Excel adds the range itself.
Example with text
Regions are in A2:A100 and sales in C2:C100:
=SUMIF(A2:A100, "North", C2:C100)
Example with numbers
To add only values above 500: =SUMIF(C2:C100, ">500").
Using a cell as the condition
=SUMIF(A2:A100, F1, C2:C100) uses whatever is typed in F1. For "greater than" with a cell, write ">"&F1.
Partial text with wildcards
=SUMIF(B2:B100, "*Pro*", C2:C100) adds every row where the product name contains Pro.
Common mistakes
- Forgetting the quotes around text and operators.
- Putting the sum range first. In SUMIF the sum range is last (it is first in SUMIFS).
- Numbers stored as text, which SUMIF will not add.
Need more than one condition? Use SUMIFS.