SUMIFS adds up values from rows that meet every one of several conditions, for example revenue from Monitors sold in the West.
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
sum_rangecriteria_range1criterion1criteria_range2, criterion2, ...How many Monitor units were sold in the West? Answer in E2.
Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.
Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Product | Units | Revenue |
| 2 | Ava | West | Monitor | 12 | $2,400.00 |
| 3 | Liam | East | Keyboard | 30 | $1,500.00 |
| 4 | Maya | West | Mouse | 45 | $900.00 |
| 5 | Noah | North | Monitor | 8 | $1,600.00 |
| 6 | Zara | East | Monitor | 15 | $3,000.00 |
| 7 | Omar | West | Keyboard | 22 | $1,100.00 |
| Formula | Result | What it shows |
|---|---|---|
=SUMIFS(E2:E7, B2:B7, "West", C2:C7, "Monitor") | $2,400.00 | West monitors only. |
=SUMIFS(D2:D7, B2:B7, "East", E2:E7, ">2000") | 15 | Units from big East deals. |
=SUMIFS(E2:E7, D2:D7, ">=10", D2:D7, "<=30") | $8,000.00 | Revenue where units are between 10 and 30. |
Learning step by step? SUMIFS is covered in Lesson 14: SUMIFS Function of the free course.