SUMIFS adds numbers only when every specified condition matches. This example totals the approved costs for project A.
Visual walkthrough
Before
Project A has approved and pending costs.
After
Only the approved project A rows are added: 2,800,000.
Select project B in the demo to get 3,000,000.
Try it here
Change the selection to see the result and matching data rows.
=SUMIFS(C2:C5,A2:A5,E2,B2:B5,"Approved")| Project | Status | Cost |
|---|---|---|
| A | Approved | 2,000,000 |
| A | Pending | 500,000 |
| B | Approved | 3,000,000 |
| A | Approved | 800,000 |
Sample data
Download sample CSV| Project (A) | Status (B) | Cost (C) |
|---|---|---|
| A | Approved | 2000000 |
| A | Pending | 500000 |
| B | Approved | 3000000 |
| A | Approved | 800000 |
If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.
Step-by-step instructions
- Enter the sample data in A1:C5. Make sure the costs in column C are numbers.
- Select an empty cell and enter the formula below.
=SUMIFS(C2:C5,A2:A5,"A",B2:B5,"Approved") - Press Enter. The two approved rows for project A total 2800000.
- Replace "A" with "B" to return 3000000. For a reusable formula, replace the project criterion with a cell reference such as E2.
Frequently asked questions
How do I sum costs after a date?
Use a date criterion such as ">="&E2, where E2 contains a real Excel date. Add a second date condition for an end date.
Reference: Microsoft Support