Excel + Google Sheets · Deep guide
SUMIFS in Excel: add with several conditions at once
SUMIF is perfect when one condition is enough, but real reports usually need more: spending in one category above a minimum, sales of one product in one month, hours of one client in one week. Stacking helper columns to cover those cases makes a sheet heavy and fragile. SUMIFS solves it by accepting as many conditions as you need.
SUMIFS works in Excel 2010 or later, in Excel 365 and in Google Sheets. In this guide you will see the syntax, a worked example with real numbers and the mistakes that most often give a wrong total, starting with the argument order, which is different from SUMIF. See also: SUMIF guide and COUNTIF guide.
How SUMIFS works
SUMIFS takes the range to add first, and then pairs of arguments: a range to check and the condition for it. Write the formula like this:
=SUMIFS(A2:A6,B2:B6,"b",A2:A6,">20")Here Excel adds column A only for the rows where column B is the letter b and the amount in column A is above 20. Every pair must have ranges of the same size, and all the conditions must be true at the same time. Works in Excel 2010 or later.
Step by step example
- Type the amounts in column A and the category of each one in column B.
- Click an empty cell, for example D2.
- Type
=SUMIFS(A2:A6,B2:B6,"b",A2:A6,">20")and press Enter. - Compare the result with the table below.
| A | B | |
|---|---|---|
| 1 | Amount (A) | Category (B) |
| 2 | 10 | a |
| 3 | 20 | b |
| 4 | 30 | c |
| 5 | 40 | b |
| 6 | 50 | d |
The expected result is 40: category b appears twice (20 and 40), but only 40 is above 20. Change the second condition to ">20" or to another operator to see how the total moves.
Common errors
- The range to add is written last, as in SUMIF. In SUMIFS it comes first and the formula returns a wrong total.
- The ranges have different sizes. Every range in the formula must have the same number of rows.
- No row meets all the conditions and the result is 0. That is correct, not an error.
- The numbers are stored as text and the numeric condition ignores them. Convert them to numbers.
Because every condition must be true, SUMIFS acts as an AND. If you need an OR, add two SUMIFS together. Compare the first result with a manual filter to be sure the conditions are written as you intended.
The same formula in other languages
- English SUMIFS
- Español SUMAR.SI.CONJUNTO
- Português SOMASES
- Deutsch SUMMEWENNS