How to Use SUMIFS Formula with Multiple Criteria in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some of the easiest ways to use the SUMIFS formula with multiple criteria, then you will find this article useful. Sometimes you may not need to sum up all the values of a data table rather than you want to add up values only based on some criteria.
So, let’s dive into the article to get to know about the ways of adding up values based on multiple criteria.


How to Use SUMIFS formula with Multiple Criteria: 11 Ways

I have the following data table where I have different columns like Fruits, Region, Vendor, Quantity, Delivery Date, Sales, etc. I will sum up the values of Quantity or Sales based on different criteria and the date format here is mm-dd-yyyy.
For this purpose, I am using Microsoft Excel 365 version, but you can use any other versions at your convenience.

SUMIFS with multiple criteria


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

Here, I will sum up the values of the sales based on three criteria like for Fruit as Apple, Vendor as Jack, and for Quantity greater than 100. To do this I will use the SUMIFS function.

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step-01:
In this case, the output cell is G8
➤Type the following formula in Cell 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 are the criteria range
G5, G6, and “>”&100 are the criteria.
When all of the criteria will be met, then the corresponding sales values will be summed up.

How to Use SUMIFS Formula with Multiple Criteria in Excel

➤Press ENTER

Result:
Then, 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 SUMIFS Function for Date Range

You can get the total Sales for November Month by using the SUMIFS function and the DATE function.

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

Step-01:
Here, the output cell is G6
➤Type the following formula in Cell 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 where DATE will return the first date of a month.
"<="&DATE(2021,11,30) is used as the second criteria where DATE will return the last date of a month.

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

➤Press ENTER

Result:
Now, you will get the sum of sales for November Month.

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


Method-3: Using SUMIFS Function for Date Range based on Criteria

Let’s say, you want to sum up the Sales for November month for Jack as Vendor. You can do this by using the SUMIFS function and the DATE function.

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

Step-01:
➤Type the following formula in Cell 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 where DATE will return the first date of a month.
“<=”&DATE(2021,11,30) is used as the second criteria where 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:
In this way, you will get the sum of sales for November Month for Jack as Vendor.

How to Use SUMIFS Formula with Multiple Criteria in Excel


Method-4: Using SUM Array Formula for Multiple Criteria

You can use an array formula using the SUM function to get the sum of sales for multiple criteria.

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

Step-01:
➤Type the following formula in Cell 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 three criteria based on which the sales will be added up.

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

➤Press ENTER

Result:
Afterward, you will get 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 other than Microsoft Excel 365 version, you have to press CTRL+SHIFT+ENTER instead of ENTER.


Method-5: Using SUMIFS Function for Empty or Non-Empty Cells

Here, in the following data table, I have blank cells in the Delivery Date column for the Fruits which have not been delivered yet. I will use the SUMIFS function for summing up the quantities based on empty Delivery Date and non-empty Order Date.

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

Step-01:
➤Type the following formula in the output cell

=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:
Then, 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 SUMIF + SUMIF for Multiple OR Criteria

If you want to sum up values based on multiple OR criteria (sum up values when at least one condition is met), then you can add two or more SUMIF functions according to your need.

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step-01:
➤Type the following formula in the output cell G8

=SUMIF(D5:D11,G6,E5:E11)+SUMIF(D5:D11,G7,E5:E11)

This formula will add up the Sales values for Vendor Jack and Nick and if only one criterion is met here then the values will be added.

How to Use SUMIFS Formula with Multiple Criteria in Excel

➤Press ENTER

Result:
Afterward, you will get the Sum of Sales for Jack and Nick.

How to Use SUMIFS Formula with Multiple Criteria in Excel

 


Method-7: Using SUM and SUMIF Function with Array Formula

You can use the SUM function and the SUMIF function for adding up values with multiple OR criteria.

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

Step-01:
Here, the output cell is G8
➤Type the following formula in the output cell 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 up the Sales values for Vendor Jack and Nick and if only one criterion is met here then the values will be added.

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

➤Press ENTER

Result:
After that, you will get 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 other than Microsoft Excel 365 version, you have to press CTRL+SHIFT+ENTER instead of ENTER.


Method-8: Using SUMIFS + SUMIFS formula with Multiple Criteria

You can add up any number of the SUMIFS function to add up values for multiple OR criteria.

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step-01:
Here, the output cell is G8
➤Type the following formula in the output cell 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)

Here, two SUMIFS functions will add Sales value for Jack and Nick for November month.

How to Use SUMIFS Formula with Multiple Criteria in Excel

➤Press ENTER

Result:
Then, you will get the Sum of Sales for Jack and Nick in November month.

SUMIFS with multiple criteria


Method-9: Using SUMPRODUCT and SUMIF Function for Multiple Criteria

You can use the SUMPRODUCT function and the SUMIF function for adding up values based on multiple criteria.

How to Use SUMPRODUCT SUMIFS Formula with Multiple Criteria in Excel

Step-01:
➤Type the following formula in the output cell G8

=SUMPRODUCT(SUMIF(D5:D11,G6:G7,E5:E11))

Here, 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:
Then, you will get the Sum of Sales for Jack and Nick.

How to Use SUMPRODUCT SUMIFS Formula with Multiple Criteria in Excel


Method-10: Using SUM and SUMIFS Function with Array

If you want to sum up values based on multiple criteria, then you can use the SUM function and the SUMIFS function.

How to Use SUMIFS Formula with Multiple Criteria in Excel

Step-01:
➤Type 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)))

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

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

How to Use SUMIFS Formula with Multiple Criteria in Excel

➤Press ENTER

Result:
After that, you will get the Sum of Sales for Jack and Nick in November month.

How to Use SUMIFS Formula with Multiple Criteria in Excel

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


Method-11: Using SUMPRODUCT and MATCH Function for Multiple Criteria

If you want to sum up values based on multiple criteria, then you can use the SUMPRODUCT function and the MATCH function.

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

Step-01:
➤Type 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 then it will return TRUE otherwise FALSE. To convert them into 1 or 0, I have added double negation(–) before all of the criteria.

MATCH(D5:D11, G6:G7,0) will match up to Jack and Nick in the Vendor’s range.
ISNUMBER will check whether there is a number or not and will return TRUE or FALSE.
After matching up all of the criteria SUMPRODUCT will add up the values in the E5:E11 range.

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

➤Press ENTER

Result:
Afterward, you will get the Sum of Sales for Jack and Nick in November month.

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


Download Practice Workbook


Conclusion

In this article, I tried to cover the easiest ways to use the SUMIFS formula with multiple criteria in Excel effectively. Hope you will find it useful. If you have any suggestions or questions, feel free to share them with us.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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