The SUMIFS function
Adds the cells that meet every one of several conditions. The sum range comes first.
How to use SUMIFS
SUMIFS adds amounts when several conditions are true at the same time. Think of a sales list where you only want the North region and only shoes: a row counts when it passes every test.
The big difference from SUMIF is the argument order. SUMIFS starts with the range to add, then takes pairs of a range to check and its condition: =SUMIFS(C2:C10,A2:A10,"North",B2:B10,"Shoes"). Because the sum range comes first, you can add as many pairs as you need, but every range must have the same number of rows.
The conditions are joined with AND. For a period, use the same date column twice, once with ">="&F1 and once with "<="&F2. Operators, wildcards such as "*shoe*", and cell references work as in SUMIF.
With a single condition, SUMIF is a little shorter. To count the matching rows instead of adding them, use COUNTIFS, which has no sum range at all. When either of two conditions may be true, add two SUMIFS together.
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)- sum_range
- The cells to add.
- criteria_range1
- The cells the first condition is checked against.
- criteria1
- The first condition.
- criteria_range2, criteria2 (optional)
- More range and condition pairs, up to 127.
Examples
=SUMIFS(1C2:C6,2A2:A6,3"North",4B2:B6,5"Shoes")| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 920 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
In plain words: Add up 1the sales in C2:C6 on the rows where 2the region in A2:A6 is 3North and 4the product in B2:B6 is 5Shoes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | 300 | |
| 2 | North | Shoes | 500 | ||
| 3 | South | Shoes | 300 | ||
| 4 | North | Bags | 250 | ||
| 5 | North | Shoes | 420 | ||
| 6 | South | Bags | 180 |
Common SUMIFS mistakes
SUMIFS returns #VALUE!
The ranges are not the same size, as in
=SUMIFS(C2:C6,A2:A7,"North"). Make every range cover the same rows.Changing SUMIF to SUMIFS gives 0, an error or the wrong total
The sum range moved. SUMIF ends with it and SUMIFS starts with it, so
=SUMIF(A2:A6,"North",C2:C6)becomes=SUMIFS(C2:C6,A2:A6,"North").SUMIFS returns 0 when I ask for two values in the same column
The conditions are joined with AND, and no cell is both North and South, so
=SUMIFS(C2:C6,A2:A6,"North",A2:A6,"South")gives 0. Add two SUMIFS instead:=SUMIFS(C2:C6,A2:A6,"North")+SUMIFS(C2:C6,A2:A6,"South").A date condition adds nothing
A date typed inside quotes depends on the date settings of the file, and dates stored as text never match. Build the date with DATE:
=SUMIFS(C2:C100,A2:A100,">="&DATE(2024,1,1),A2:A100,"<"&DATE(2024,2,1))adds up January. Also check that the dates are real dates and not text.
Practice SUMIFS
Read the guide
- Excel test for a job interview: what it looks like and how to practice
- Pivot tables explained, and the formulas that do the same job
- Excel for finance and accounting: the skills you actually use
- Dates in Excel: how they work and the formulas you need
- Excel practice data: free datasets with tasks and answers
- Pivot table practice: 15 exercises with answers on real sales data
- Excel finance data: free practice file with tasks and answers
Questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes one or more conditions and puts the sum range first.
How do I sum between two dates?
Use the date column twice, with the start date in F1 and the end date in F2: =SUMIFS(C2:C100,A2:A100,">="&F1,A2:A100,"<="&F2).
Can SUMIFS use OR instead of AND?
No, the conditions are always joined with AND. When either condition may be true, add two SUMIFS: =SUMIFS(C2:C6,A2:A6,"North")+SUMIFS(C2:C6,A2:A6,"South"). SUMPRODUCT can also do it in one formula.
Is SUMIFS case sensitive?
No. Text conditions ignore upper and lower case, so "north" and "North" match the same cells.