Conditional totals
SUMIFS: total sales that match two conditions
A sales total is only useful when it includes the right orders. Use SUMIFS to add amounts for one region and one order status, without hiding or deleting the other rows.
The question: how much has North actually shipped?
Imagine a small stationery supplier reviewing five orders. The manager wants the value of shipped orders in the North region. Pending orders should not count, and neither should orders for South. Enter these headers in A1:C1 and the five orders in rows 2–6.
| Region (A) | Status (B) | Amount (C) |
|---|---|---|
| North | Shipped | 240 |
| South | Shipped | 180 |
| North | Pending | 95 |
| North | Shipped | 160 |
| South | Pending | 75 |
Write the formula
SUMIFS starts with the numbers to add. Then provide a range to inspect and the condition that range must meet. Repeat the range-and-condition pair for the second condition.
=SUMIFS(C2:C6,A2:A6,"North",B2:B6,"Shipped")Check the answer before trusting it
The result is 400. Only rows 2 and 5 match both conditions: 240 + 160 = 400. The 95 pending order is in North but fails the status condition. The 180 shipped order passes the status check but fails the region check.
This is AND logic: every condition must match on the same row. Summing all North orders instead would return 495. Summing every shipped order would return 580. Those are useful cross-checks when diagnosing a wrong result.
Make the report reusable
Put North in E2 and Shipped in F2. Refer to those cells instead of typing the criteria inside the formula. Someone reading the sheet can then change the selection without editing the formula.
The dollar signs below keep the source ranges fixed if you copy the formula down. E2 and F2 remain relative, so a second report row can use its own selections.
=SUMIFS($C$2:$C$6,$A$2:$A$6,E2,$B$2:$B$6,F2)Three mistakes worth checking
Keep every range the same size. C2:C6, A2:A6 and B2:B6 each contain five rows. Accidentally using B2:B5 leaves a shorter criteria range.
Store amounts as numbers. A cell containing a currency symbol pasted as text can look right while failing to contribute to the total. Formatting a numeric cell as currency is different from storing the whole amount as text.
Check imported labels for extra spaces. North and North followed by a space may look identical in a narrow cell. Clean the source labels before blaming the formula.
Try a small change
Change the pending North order from 95 to 125. The shipped total should stay at 400 because that row still fails the status condition. Now change its status to Shipped. The total should become 525. This checks both which rows qualify and which numbers get added.
Try the example
Use the original values or change them to test your understanding.