How to Use SUMIFS Formula with Multiple Criteria in Excel (11 Ways)

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.

Download Workbook

11 Ways to Use SUMIFS formula with Multiple Criteria

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

SUMIFS for multiple criteria

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.

SUMIFS for multiple criteria

➤Press ENTER

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

SUMIFS for multiple criteria

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.

SUMIFS formula with multiple criteria

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.

SUMIFS for dates

➤Press ENTER

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

SUMIFS for dates

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.

SUMIFS formula with multiple criteria

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.

SUMIFS for dates based on criteria

➤Press ENTER

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

SUMIFS for dates based on criteria

Method-4: Using SUM Array Formula for Multiple Criteria

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

SUMIFS formula with multiple criteria

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.

SUM array formula

➤Press ENTER

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

SUM array formula

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

SUMIFS formula with multiple criteria

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)

Empty or non-empty cells

➤Press ENTER

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

Empty or non-empty cells

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.

SUMIFS formula with multiple criteria

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.

SUMIF+SUMIF

➤Press ENTER

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

SUMIF+SUMIF


Similar Readings:


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.

SUM and SUMIF with array

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.

SUM and SUMIF with array

➤Press ENTER

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

SUM and SUMIF with array

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

SUMIFS formula with multiple criteria

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.

SUMIFS+SUMIFS

➤Press ENTER

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

SUMIFS+SUMIFS

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.

SUMPRODUCT & SUMIF

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.

SUMPRODUCT & SUMIF

➤Press ENTER

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

SUMPRODUCT & SUMIF

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.

SUM+SUMIFS

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.

SUM+SUMIFS

➤Press ENTER

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

SUM+SUMIFS

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

SUMIFS formula with multiple criteria

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.

SUMPRODUCT+MATCH

➤Press ENTER

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

SUMPRODUCT+MATCH

Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

PRACTICE

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.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo