Excel track · Exercise 2 of 86
SUMIFS: two conditions at once
Total the rows that meet two conditions at once instead of filtering the table first.
Advanced
Repo sales by region and shipped orders by value
Read-only data · screen 1 of 2 · answer in C7
In C7, total the North amounts Ada sold with a function that takes two conditions.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | North | Ada | 320 |
| 3 | South | Ben | 250 |
| 4 | North | Cleo | 180 |
| 5 | West | Dev | 300 |
| 6 | North | Ada | 400 |
| 7 | North, Ada total |
In C7, total the shipped orders that came to more than 200.
| A | B | C | |
|---|---|---|---|
| 1 | Product | Status | Amount |
| 2 | Widget | Shipped | 320 |
| 3 | Gadget | Pending | 250 |
| 4 | Widget | Shipped | 180 |
| 5 | Gadget | Shipped | 300 |
| 6 | Widget | Pending | 400 |
| 7 | Shipped over 200 |
Reference answer
What it does
SUMIFS adds the cells in one range whose rows meet every condition you set. It takes the range to add first, then pairs of range and condition. That is the opposite order to SUMIF, and the reason a SUMIF habit gets it wrong.
Here the sum range is C2:C6, the region must read North and the rep must read Ada. Rows 2 and 6 match, so the answer is 720. The second screen drops the rep and adds an amount above 200, which leaves out the shipped order at 180 and gives 620.
Every criteria range has to be the same shape as the sum range, or the rows drift and the total is wrong. Text and comparisons both go in quotes, and wildcards work inside them. A condition that needs OR across two columns takes two SUMIFS calls added together.
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)Worked example
On Repo sales by region and shipped orders by value, =SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Ada") returns 720.
=SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Ada")Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | A criteria range is a different shape from the sum range, so the rows no longer line up. Every range in the call has to start and end on the same rows. |
#NAME? | The function is typed as SUMIF, which takes its arguments in another order: range, condition, sum range. |
Wrong total | SUMIFS joins conditions with AND. Two tests that exclude each other, such as Shipped and Open on the same column, can never both be true for one row, and the answer comes back as 0. |
At work
A sales sheet totalling one rep in one region, and an order book totalling the shipped orders above a credit limit.