Office Dễ Hiểu

How to use SUMIFS with multiple criteria in Excel

Add approved costs for one project using two matching conditions.

SUMIFS adds numbers only when every specified condition matches. This example totals the approved costs for project A.

Visual walkthrough

ExcelSUMIFSproject and status

Before

Project A has approved and pending costs.

After

Only the approved project A rows are added: 2,800,000.

Check the result

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")
ProjectStatusCost
AApproved2,000,000
APending500,000
BApproved3,000,000
AApproved800,000
Result2,800,000
Project (A)Status (B)Cost (C)
AApproved2000000
APending500000
BApproved3000000
AApproved800000

If the CSV opens in one column, choose Data → From Text/CSV and select the comma delimiter.

Step-by-step instructions

  1. Enter the sample data in A1:C5. Make sure the costs in column C are numbers.
  2. Select an empty cell and enter the formula below.
    =SUMIFS(C2:C5,A2:A5,"A",B2:B5,"Approved")
  3. Press Enter. The two approved rows for project A total 2800000.
  4. 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