How to Use the SUMIFS Formula with Multiple Criteria in Excel – 11 Methods

The sample dataset showcases Fruits, Region, Vendor, Quantity, Delivery Date and Sales.

You want to sum Sales or Quantity values based on different criteria.

SUMIFS with multiple criteria


Method 1: Using the SUMIFS function for Multiple Criteria with a Comparison Operator

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • The output cell is G8
  • Enter the following formula in G8.
=SUMIFS(E5:E11,B5:B11,G5,C5:C11,G6,D5:D11,">"&100)

E5:E11 is the sum range
And B5:B11, C5:C11, and D5:D11 is the criteria range
G5, G6, and “>”&100 are the criteria.
If all criteria are met,  sales values will be summed.

How to Use SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will get the total sales for Apple as Fruit, Vendor as Jack, and Quantity greater than 100.

How to Use SUMIFS Formula with Multiple Criteria in Excel


Method 2 – Using the SUMIFS Function for a Date Range

 

How to Use SUMIFS Formula for Dates with Multiple Criteria in Excel

Step 1:

  • The output cell is G6.
  • Enter the following formula in G6.
=SUMIFS(E5:E11,D5:D11,">="&DATE(2021,11,1),D5:D11,"<="&DATE(2021,11,30))

E5:E11 is the range of Sales, D5:D11 is the criteria range which includes the Dates.
">="&DATE(2021,11,1) is the first criteria: DATE will return the first date of a month.
"<="&DATE(2021,11,30) is used as the second criteria: DATE will return the last date of a month.

How to Use SUMIFS Formula for Dates with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will get the sum of sales for November.

How to Use SUMIFS Formula for Dates with Multiple Criteria in Excel


Method 3 – Using the SUMIFS Function for a Date Range based on Criteria

You want to sum Jack’s Sales in November.

How to Use SUMIFS Formula for Dates with Multiple Criteria in Excel

Step 1:

  • Enter the following formula in G7.
=SUMIFS(E5:E11,D5:D11,">="&DATE(2021,11,1),D5:D11,"<="&DATE(2021,11,30),C5:C11,G6)

E5:E11 is the range of Sales, D5:D11 is the criteria range which includes the Dates.
“>=”&DATE(2021,11,1) is the first criteria: DATE will return the first date of a month.
“<=”&DATE(2021,11,30) is used as the second criteria: DATE will return the last date of a month.
C5:C11 is the third criteria range and G6 is the criteria for this range.

How to Use SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
This is the output.

How to Use SUMIFS Formula with Multiple Criteria in Excel


Method 4 – Using a SUM Array Formula for Multiple Criteria

Apply SUM Array to Use SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • Enter the following formula in G8.
=SUM((B5:B11="Apple")*(C5:C11="Jack")*(D5:D11>100)*E5:E11)

E5:E11 is the sum range
B5:B11=”Apple”, C5:C11=”Jack”, and D5:D11>100 are the three criteria based on which the sales will be added.

Apply SUM Array to Use SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will see the total sales for Apple as Fruit, Vendor as Jack, and Quantity greater than 100.

Apply SUM Array to Use SUMIFS Formula with Multiple Criteria in Excel

Note:
If you are using a version, other than Microsoft Excel 365, you must press CTRL+SHIFT+ENTER instead of ENTER.


Method 5 – Using the SUMIFS Function for Empty or Non-Empty Cells

Use SUMIFS Formula with Multiple Criteria in Excel with empty or non-empty cell

Step1:

  • Enter the following formula in the output cell: B14.
=SUMIFS(E5:E11,C5:C11,"<> ",D5:D11,"=")

E5:E11 is the sum range
C5:C11 is the range of Order Date and “<> “ is the criteria for this range which means not equal to Blank.
The range of Delivery Date is D5:D11 and “=” is the criteria for this range which means equal to Blank. ( You can use ” ” instead of “=” also)

Use SUMIFS Formula with Multiple Criteria in Excel with empty or non-empty cell

  • Press ENTER.

Result:
You will get the Quantity of Not Delivered Products.

Use SUMIFS Formula with Multiple Criteria in Excel with empty or non-empty cell


Method 6 – Using the SUMIF + SUMIF function for Multiple OR Criteria

 

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • Enter the following formula in the output cell: G8.
=SUMIF(D5:D11,G6,E5:E11)+SUMIF(D5:D11,G7,E5:E11)

This formula will add the Sales values for Vendors Jack and Nick and if one criterion is met, the values will be added.

How to Use SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will see the Sum of Sales for Jack and Nick.

How to Use SUMIFS Formula with Multiple Criteria in Excel

 


Method 7 – Using the SUM and SUMIF Functions with an Array Formula

 

How to Use SUM and SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • The output cell is G8.
  • Enter the following formula in G8.
=SUM(SUMIF(D5:D11,{"Jack","Nick"},E5:E11))

D5:D11 is the criteria range, {“Jack”, “Nick”} is the array of criteria and E5:E11 is the sum range.
Then SUM will add the Sales values for Vendors Jack and Nick and if one criterion is met, the values will be added.

How to Use SUM and SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will see the Sum of Sales for Jack and Nick.

How to Use SUM and SUMIFS Formula with Multiple Criteria in Excel

Note:
If you are using a version, other than Microsoft Excel 365, you must press CTRL+SHIFT+ENTER instead of ENTER.


Method 8 – Using the SUMIFS + SUMIFS formula with Multiple Criteria

 

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • The output cell is G8
  • Enter the following formula in G8.

=SUMIFS(E5:E11,C5:C11,">="&DATE(2021,11,1),C5:C11,"<="&DATE(2021,11,30),D5:D11,G6)+SUMIFS(E5:E11,C5:C11,">="&DATE(2021,11,1),C5:C11,"<="&DATE(2021,11,30),D5:D11,G7)

The two SUMIFS functions will add Sales value for Jack and Nick in November.

How to Use SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will see the Sum of Sales for Jack and Nick in November.

SUMIFS with multiple criteria


Method 9 – Using the SUMPRODUCT and the SUMIF Function for Multiple Criteria

How to Use SUMPRODUCT SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • Enter the following formula in the output cell: G8.
=SUMPRODUCT(SUMIF(D5:D11,G6:G7,E5:E11))

D5:D11 is the criteria range, G6:G7 is multiple criteria in a range and E5:E11 is the sum range.

How to Use SUMPRODUCT SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will see the Sum of Sales for Jack and Nick.

How to Use SUMPRODUCT SUMIFS Formula with Multiple Criteria in Excel


Method 10 – Using the SUM and the SUMIFS Function with an Array

 

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • Enter the following formula in the output cell: G8.
=SUM(SUMIFS(E5:E11,D5:D11,{"Jack","Nick"},C5:C11,">="&DATE(2021,11,1),C5:C11,"<="&DATE(2021,11,30)))

D5:D11 is the first criteria range, {“Jack”, “Nick”} is the array of criteria and E5:E11 is the sum range.

C5:C11 is the second and third criteria range
">="&DATE(2021,11,1) is the second criteria: DATE will return the first date of a month.
"<="&DATE(2021,11,30) is used as the third criteria : DATE will return the last date of a month.

How to Use SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will get the Sum of Sales for Jack and Nick in November.

How to Use SUMIFS Formula with Multiple Criteria in Excel

Note:
If you are using a version, other than Microsoft Excel 365, you must press CTRL+SHIFT+ENTER instead of ENTER.


Method 11 – Using the SUMPRODUCT and the MATCH Functions for Multiple Criteria

 

How to Use SUMPRODUCT, MATCH and SUMIFS Formula with Multiple Criteria in Excel

Step 1:

  • Enter the following formula in the output cell: G8.
=SUMPRODUCT(–(C5:C11>=DATE(2021,11,1)),–(C5:C11<=DATE(2021,11,30)),–(ISNUMBER(MATCH(D5:D11,G6:G7,0))),E5:E11)

Within the SUMPRODUCT function, there are three criteria, if they are fulfilled, it will return TRUE. Otherwise, FALSE. To convert the criteria into 1 or 0, a double negation(–) was used.

MATCH(D5:D11, G6:G7,0) matches Jack and Nick in the Vendor’s range.
ISNUMBER checks whether there is a number, and returns TRUE or FALSE.
After matching all the criteria, the SUMPRODUCT will add the values in E5:E11.

How to Use SUMPRODUCT, MATCH and SUMIFS Formula with Multiple Criteria in Excel

  • Press ENTER.

Result:
You will see the Sum of Sales for Jack and Nick in November.

How to Use SUMPRODUCT, MATCH and SUMIFS Formula with Multiple Criteria in Excel


Download Practice Workbook

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo