The SUMIFS function
Adding up by several criteria at once: North sales, delivered, in March. Every condition must be met.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
In a French Excel: =SOMME.SI.ENS(somme_plage; plage_critères1; critère1; [plage_critères2; critère2]; …).
Arguments
- sum_range: the column to add up, first.
- criteria_range1, criteria1…: pairs of tested column and criterion. Up to 127 pairs.
An example
=SUMIFS(D:D,A:A,"North",C:C,">="&DATE(2024,3,1),C:C,"<"&DATE(2024,4,1))
Total North sales in March 2024: the date is tested twice, to bound the month.
Pitfalls
- Every range must be the same size: otherwise, #VALUE!.
- Criteria combine with AND: for an OR, add two SUMIFS.
- Dates stored as text meet no date criterion: Fix dates.
Going further
- SUMIF.
- COUNTIFS.
- Find the right formula: "add up by two criteria".
Frequently asked questions
- How do I do an OR in SUMIFS?
- Add two formulas:
=SUMIFS(D:D,A:A,"North")+SUMIFS(D:D,A:A,"East"), or=SUM(SUMIF(A:A,{"North","East"},D:D)).