SUMIF with multiple criteria in Excel
To sum with more than one condition, use SUMIFS, which is SUMIF with several criteria. The sum range comes first, then each pair of a range and a condition. A row is added only when it meets every condition, which is AND logic. For OR logic, add two SUMIFS together.
The examples below use a small sales table, so you can check each result by hand. Practice the same patterns in the conditional sums lesson at the end.
1. The SUMIFS pattern
The pattern is =SUMIFS(sum_range, range1, criteria1, range2, criteria2, ...). Note that the sum range is the first argument, which is the opposite order of SUMIF. All the ranges must have the same size and start in the same row.
Example: North region and Pen product
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Total | |
| 2 | North | Pen | 10 | 50 | |
| 3 | North | Ink | 20 | ||
| 4 | South | Pen | 30 | ||
| 5 | North | Pen | 40 | ||
| 6 | South | Ink | 50 |
Only rows 2 and 5 are both North and Pen, so the result is 10 + 40 = 50.
2. Conditions with numbers and dates
A criterion can hold an operator in quotation marks: ">=30", "<>0" or "<"&E1 to compare with a cell. The same range can appear twice, which gives you a between condition, such as sales of at least 20 and less than 50. Dates work the same way, for example ">="&DATE(2026,1,1).
Example: Sales of 30 or more
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Total | |
| 2 | North | Pen | 10 | 120 | |
| 3 | North | Ink | 20 | ||
| 4 | South | Pen | 30 | ||
| 5 | North | Pen | 40 | ||
| 6 | South | Ink | 50 |
The rows with 30, 40 and 50 meet the condition: 30 + 40 + 50 = 120.
3. OR logic: add two SUMIFS
SUMIFS needs every condition to be true. If a row may match either of two values in the same column, calculate each case on its own and add the results. This is safe because one cell cannot hold both values, so no row is counted twice.
Example: North region, Pen or Ink
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Total | |
| 2 | North | Pen | 10 | 70 | |
| 3 | North | Ink | 20 | ||
| 4 | South | Pen | 30 | ||
| 5 | North | Pen | 40 | ||
| 6 | South | Ink | 50 |
North and Pen gives 10 + 40 = 50. North and Ink gives 20. The total is 70.
4. Wildcards and cell criteria
Use * for any text and ? for one character inside a text criterion, for example "Pe*" for every product that starts with Pe. Put the criteria in cells and join them with &, as in ">="&F1, so you can change a condition without editing the formula. For a fully flexible calculation, SUMPRODUCT can combine conditions with arithmetic.
Common mistakes
The sum range is in the wrong place. In SUMIFS it is the first argument, not the last.
Fix: Write the range to add first, then each range and criterion in pairs.
Ranges of different sizes. Excel returns #VALUE! when the ranges do not match.
Fix: Make every range start and end in the same rows.
Trying to use OR inside one SUMIFS. Two conditions on different ranges are always AND.
Fix: Add two SUMIFS, one for each alternative.
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition and puts the sum range last. SUMIFS takes any number of conditions and puts the sum range first. SUMIFS also works with one condition, so many people use it every time.
How many criteria can SUMIFS handle?
Up to 127 pairs of a range and a criterion.
How do I count instead of sum?
Use COUNTIFS with the same pairs of ranges and criteria, and leave out the sum range.
Related functions
- SUMIFSSUMIFS adds up values that meet several conditions at once, such as paid orders from one region.
- SUMIFSUMIF adds up the values that meet one condition, such as all sales of one region or all amounts above 100.
- COUNTIFSCOUNTIFS counts the rows that meet several conditions at once, such as a region and an amount range.
- SUMPRODUCTSUMPRODUCT multiplies ranges row by row and adds the results: total revenue, weighted averages, and sums with several conditions.
More on SUMIFS conditions
The guide covers AND logic with SUMIFS and OR logic with two sums. This piece adds how to keep the conditions in cells, how to sum between two dates and how to check a SUMIFS result by hand.
Keep the conditions in cells
A formula with the words typed inside it is hard to reuse. Put each condition in its own cell and point at it. For a text condition the cell is used as it is. For a comparison, join the operator and the cell with &, because the operator has to be part of the criterion text.
=SUMIFS(C2:C6,A2:A6,F1,B2:B6,F2)
=SUMIFS(C2:C6,C2:C6,">="&F3)Now a reader can change a condition and see the total change, without opening the formula.
Sum between two dates
A between condition is two conditions on the same range: one for the start and one for the end. Put the start date in F1 and the end date in F2. Use ">=" for the start so that the first day is included, and "<=" for the end so that the last day is included. Dates are numbers, so the comparison works as it does for any number.
=SUMIFS(C2:C100,A2:A100,">="&F1,A2:A100,"<="&F2)Check the result by hand
A wrong SUMIFS rarely shows an error. It shows a believable number. Test it on a small slice: filter the data to the same conditions and look at the status bar, which shows the sum of the selected cells. If the two numbers differ, look for text numbers, extra spaces or a date typed as text.
Count the matching rows with COUNTIFS and the same conditions. A count of zero tells you that no row matches, so the zero total is correct and the conditions are the problem. For more on this, see [the conditional sums lesson](/learn/conditional-sums) and [how wildcards work](/guides/wildcards).