The SUMIF function
Adding up only the rows that meet a criterion: North sales, amounts over 1,000, a customer's invoices.
Syntax
=SUMIF(range, criteria, [sum_range])
In a French Excel: =SOMME.SI(plage; critère; [somme_plage]).
Arguments
- range: the column tested.
- criteria:
"North",">1000",A2,"Smi*"(wildcards * and ?). - sum_range: the column to add up; omitted, it is the tested range itself.
An example
=SUMIF(A:A,"North",C:C)
Adds up column C on the rows where column A is "North", ignoring case.
Pitfalls
- Argument order: the tested range first, the sum after, the reverse of SUMIFS.
- A comparison criterion goes in quotes, with the cell joined by &:
">"&E1. - Numbers stored as text are not added, with no error or warning.
- Extra spaces: "North" with a trailing space is not "North".
Going further
- SUMIFS, for several criteria.
- COUNTIF, to count.
- Automatic pivot table: every total per group at once.
Frequently asked questions
- Why does SUMIF return 0?
- No row meets the criterion: spaces, spelling, or a sum column whose numbers are stored as text.