Excel + Google Sheets · Deep guide
SUMIF in Excel: add only what meets a condition
If you track your expenses in a spreadsheet you soon need to know how much you spent on one category only, for example food. Adding by hand is slow and breaks the moment you add a row. The SUMIF function does the job for you and updates itself whenever the data changes.
SUMIF works in Excel 2010 or later, in Excel 365 and in Google Sheets, so you can use it on almost any computer. In this guide you will see the syntax, a worked example with real numbers and the mistakes we see most often in our customers' templates. See also: VLOOKUP guide and COUNTIF guide.
How SUMIF works
The function takes three pieces of information: the range where the condition is searched, the condition itself and the range that is added up. Write the formula like this:
=SUMIF(B2:B6,"b",A2:A6)Here Excel looks for the letter b in column B and adds the amounts in column A that sit on the same row. With only two arguments it adds the same range it searches. 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
=SUMIF(B2:B6,"b",A2:A6)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 60, because the category b appears twice (20 and 40). You can replace the condition with an operator such as ">25" to add only the amounts above 25.
Common errors
- The ranges have different sizes and the result is wrong. Check that both ranges have the same number of rows.
- There are trailing spaces in the text, for example b followed by a space. Use TRIM to clean the column.
- The category does not exist and the result is 0. That is not an error: it means no row meets the condition.
- The numbers are stored as text and are not added. Convert them to numbers from the data menu.
Once you master SUMIF, the natural next step is SUMIFS, which accepts several conditions at once and avoids helper columns in your monthly reports. Compare the result with a manual filter the first time you use it.