How to Use SUMIFS Formula with Multiple Criteria in Excel

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.

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.

Step-01:
In this case, the output cell is G8
➤Type the following formula in Cell G8

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.

➤Press ENTER

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

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.

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

➤Press ENTER

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

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.

Step-01:
➤Type the following formula in Cell G7

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.

➤Press ENTER

Result:
In this way, you will get the sum of sales for November Month for Jack as Vendor.

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.

Step-01:
➤Type the following formula in Cell G8

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.

➤Press ENTER

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

📓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.

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

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)

➤Press ENTER

Result:
Then, you will get the Quantity of Not Delivered Products.

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.

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

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.

➤Press ENTER

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

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.

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

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.

➤Press ENTER

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

📓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.

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

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

➤Press ENTER

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

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.

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

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

➤Press ENTER

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

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.

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

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.

➤Press ENTER

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

📓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.

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

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.

➤Press ENTER

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

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.