SUMIF in Excel: add only the rows that match
SUMIF adds numbers only from the rows that meet one condition. Sales for Ana only. Amounts over 100 only.
The formula
=SUMIF(where_to_check, what_to_match, what_to_add)
What you will make
A total of Ana’s sales from a list that has everyone’s sales mixed together.
Steps
- Names in column A, amounts in column B.
- Click an empty cell, say D1.
- Type
=SUMIF(A:A, "Ana", B:B)and press Enter.
| A | B | |
|---|---|---|
| 1 | Ana | 100 |
| 2 | Raj | 50 |
| 3 | Ana | 80 |
Result: 180. Excel checks column A for “Ana” and adds the matching rows from column B.
Point at a cell instead of typing the name
=SUMIF(A:A, D1, B:B)
Now type any name in D1 and the total updates.
Conditions with numbers
| Condition | Formula |
|---|---|
| Over 100 | =SUMIF(B:B, ">100") |
| 100 or less | =SUMIF(B:B, "<=100") |
| Not Ana | =SUMIF(A:A, "<>Ana", B:B) |
| Starts with A | =SUMIF(A:A, "A*", B:B) |
When you are adding the same column you check, the third part can be left out.
Check it worked
Filter column A to Ana and look at the status bar at the bottom — the Sum shown there should match your SUMIF.
Two or more conditions
Ana and March? That is SUMIFS.
20 years with spreadsheets and enterprise systems. Writes one short, plain-English Excel lesson a day. Got an Excel question? learnexceleasycom@gmail.com
