How to Use SUMIFS Function in Excel with Multiple Criteria

If you want to sum values that meet multiple criteria then you can use the SUMIFS function. The SUMIFS function can sum cells that meet multiple criteria. If you use two different criteria, then the SUMIFS function will sum the cells that meet both of the criteria. This function generally works with AND logic. The focus of this article is to explain how you can use the SUMIFS function in Excel with multiple criteria.


Download Practice Workbook

You can download the practice workbook for the SUMIFS function in Excel with multiple criteria from the link below.


8 Suitable Examples to Use SUMIFS Function in Excel with Multiple Criteria

Here, I have taken the following dataset to explain this article. This dataset contains the State, Product, and Sales columns. I will use this dataset to explain how you can use the SUMIFS function in Excel with multiple criteria. I will show 8 different examples to illustrate the functionality of the SUMIFS function with multiple criteria more clearly.

SUMIFS Function in Excel with Multiple Criteria


1. SUMIFS Function with Multiple Criteria

In this first example, I will show you the basic use of the SUMIFS function with multiple criteria. Here, I will calculate the Total Sales for the Mobiles that are sold from Texas. I will use these 2 criteria in the SUMIFS function and the function will only add those cells that meet both criteria.

SUMIFS Function with Multiple Criteria in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want the Total Sales. Here, I selected cell G8.
  • Secondly, in cell G8 write the following formula.
=SUMIFS(D5:D14,B5:B14,G5,C5:C14,G6)

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, B5:B14 as criteria_range1, G5 as criteria1, C5:C14 as criteria_range2, and G6 as criteria2. Now, the formula will sum the values from the cell range D5:D14 that meet both criteria1 and criteria2.

  • Thirdly, press ENTER and you will get your desired result.

Read More: [Fixed]: SUMIFS Not Working with Multiple Criteria (3 Solutions)


2. Using Comparison Operator in SUMIFS Function with Multiple Criteria

In this example, I will show you how you can use the Comparison Operator in the SUMIFS function in Excel with multiple criteria. In the following dataset, you can see that I want to calculate the Total Sales for Mobile whose Sales are more than or equal to $800.

Using Comparison Operator in SUMIFS Function with Multiple Criteria in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Total Sales. Here, I selected cell G8.
  • Secondly, in cell G8 write the following formula.
=SUMIFS(D5:D14,C5:C14,G5,D5:D14,">=800")

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, C5:C14 as criteria_range1, G5 as criteria1, D5:D14 as criteria_range2, and >=800 as criteria2. Now, the formula will sum the values from the cell range D5:D14 that meet both criteria1 and criteria2.

  • Thirdly, press ENTER to get the Total Sales.

Read More: Excel SUMIFS Not Equal to Multiple Criteria (4 Examples)


3. Multiple Criteria for Dates in Excel

In this example, I will use the SUMIFS function with multiple criteria for dates in Excel. Suppose you have a dataset that contains Date, Product, and Sales. Now, you want to calculate the Total Sales for a specific Product in the last 1 week. You can do it easily by using the SUMIFS function.

SUMIFS Finction with Multiple Criteria for Dates in Excel

Let’s see how you can do it.

Steps:

  • First, select the cell where you want to calculate the Total Sales.
  • Then, in that selected cell write the following formula.
=SUMIFS(D5:D14,C5:C14,G5,B5:B14,">="&TODAY()-7,B5:B14,"<="&TODAY())

Formula Breakdown

  • TODAY() —-> Here, the TODAY function will return the current date.
    • Output: 44830
  • TODAY()-7 —-> Here, the TODAY function will return the current date and then subtract 7 from that date. Finally, the formula will return the Date before 7 days.
    • Output: 44823
  • SUMIFS(D5:D14,C5:C14,G5,B5:B14,”>=”&TODAY()-7,B5:B14,”<=”&TODAY()) —-> turns into
    • SUMIFS(D5:D14,C5:C14,G5,B5:B14,”>=44823″,B5:B14,”<=44830″) —-> Here, the SUMIFS function will return the summation of the cells in cell range D5:D14 that match all of these 3 criteria.
      • Output: 2300
  • Finally, press ENTER to get the Total Sales.

Read More: SUMIFS Sum Range Multiple Columns in Excel(6 Easy Methods)


4. Use of SUMIFS Function in Excel with Blank and Non-Blank Cells

In this example, I will show you how you can use the SUMIFS function in Excel with multiple criteria for bank and non-blank cells. For this example, I have taken the following dataset. This dataset contains Branch and Projects that are assigned to some Members and the Start Date and the End Date of the Projects are given. Suppose you want to calculate the number of Total Members working on Running Projects in Branch 1. Now, I will show you how you can do it by using the SUMIFS function.

Use of SUMIFS Function in Excel with Multiple Criteria for Blank and Non-Blank Cells

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Total Members. Here, I selected cell C20.
  • Secondly, in cell C20 write the following formula.
=SUMIFS(F5:F14,B5:B14,C17,E5:E14,"=",D5:D14,"<>")

Formula Breakdown

  • Here, in the SUMIFS function, I selected cell range F5:F14 as the sum_range, B5:B14 as criteria_range1, and C17 as criteria1.
  • Then, I selected E5:E14 as criteria_range2 and “=” as criteria2. Here, “=” means blank cells.
  • Next, I selected D5:D14 as criteria_range3 and “<>” as criteria3. Here, “<>” means non-blank cells.
  • Now, the function will return the summation of the values from cell range F5:F14 that match all of these three criteria.
  • Thirdly, press ENTER to get the Total Members.

Read More: How to Use SUMIFS with Date Range and Multiple Criteria (7 Quick Ways)


Similar Readings


5. SUMIFS Function in Excel with Multiple OR Criteria

The SUMIFS function generally works with AND logic. But you can also use this function for OR logic. Here, I will explain how you can use the SUMIFS function with multiple OR criteria. Suppose, you want to calculate the Total Sales of Headphones in Texas or Florida. I will show you how you can do that.

SUMIFS Function in Excel with Multiple OR Criteria

Let’s see the steps.

Steps:

  • Firstly, select the cells where you want to calculate the Total Sales. Here, I selected cell G8.
  • Secondly, in cell G8 write the following formula.
=SUMIFS(D5:D14,C5:C14,G5,B5:B14,"Texas")+SUMIFS(D5:D14,C5:C14,G5,B5:B14,"Florida")

Formula Breakdown

  • Here, in the 1st SUMIFS function, I selected cell range D5:D14 as sum_range, C5:C14 as criteria_range1, G5 as criteris1, B5:B14 as criteria_range2, and “Texas” as criteria2. This function will sum the values in the range D5:D14 that match both of the criteria.
  • Then, in the 2nd SUMIFS function, I selected cell range D5:D14 as sum_range, C5:C14 as criteria_range1, G5 as criteris1, B5:B14 as criteria_range2, and “Florida” as criteria2. This function will sum the values in the range D5:D14 that match both of the criteria.
  • Now, the formula will sum these two results and return the Total Sales of Headphones in Texas or Florida.
  • Finally, press ENTER to get the result.

Read More: SUMIFS Multiple Criteria Different Columns (6 Effective Ways)


6. Applying SUMIFS & SUM Functions for Multiple Criteria

In this example, I will use the SUMIFS function and the SUM function together for multiple OR criteria in Excel. The dataset of this example is the same as the previous example. Here, I will solve the same problem by using a different formula. I will show you how you can calculate the Total Sales of Headphones in Texas or Florida.

Applying SUMIFS & SUM Functions for Multiple Criteria in Excel

Let’s see the steps.

Steps:

  • First, select the cell where you want the Total Sales. Here, I selected cell G8.
  • Secondly, in cell G8 write the following formula.
=SUM(SUMIFS(D5:D14,C5:C14,G5,B5:B14,{"Texas","Florida"}))

Formula Breakdown

  • SUMIFS(D5:D14,C5:C14,G5,B5:B14,{“Texas”,”Florida”}) —-> Here, in the SUMIFS function an array was selected as criteria. This array contains 2 different values. The function will look for both of these values separately and return the sum for both.
    • Output: {1300,2200}
  • SUM(SUMIFS(D5:D14,C5:C14,G5,B5:B14,{“Texas”,”Florida”})) —-> turns into
    • SUM({1300,2200}) —-> Here, the SUM function will return the summation of these 2 values.
      • Output: 3500
  • Thirdly, press ENTER to get the result. If you are using an older version of Excel than Excel 2019 then press CTRL+SHIFT+ENTER to get the result.

Read More: SUMIFS with Multiple Criteria in the Same Column (5 Ways)


7. Using Named Range for Dynamic Criteria in SUMIFS Function in Excel

In this example, I will show you how you can use Named Range in the SUMIFS function with multiple criteria in Excel. To explain this example, I have taken the following dataset. Here, I will calculate the Total Sales for the State of Texas or Florida.

Using Named Range for Dynamic Multiple Criteria in SUMIFS Function in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell range that you want to use as the criteria.
  • Secondly, write a name for the selected range. Here, I named mine as Multiple_Criteria.

Naming Range for SUMIFS Function with Multiple Criteria in Excel

  • After that, select the cell where you want your Total Sales. Here, I selected cell G9.
  • Next, in cell G9 write the following formula.
=SUM(SUMIFS(D5:D14,B5:B14,Multiple_Criteria))

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, B5:B14 as criteria_range1, and Multiple_Criteria as criteria1. Here, Multiple_Criteria is the named range. The SUMIFS function will look for every value in the named range and return the sum for these values. And then, the SUM function will return the summation of these values as Total Sales.

  • Finally, press ENTER to get the result. If you are using an older version of Excel than Excel 2019 then press CTRL+SHIFT+ENTER to get the result.

Read More: SUMIFS Multiple Columns in Excel (5 Types of Application)


8. Use of Wildcard Character in SUMIFS Function with Multiple Criteria

In this example, I will show you how you can use Wildcard Character (~,*,?) in the SUMIFS function for multiple criteria in Excel. For this example, I will use the Asterisk (*) symbol. Suppose you have a dataset containing Sales Person, Product, and Sales column. And you want to calculate the Total Sales of Mobile from Sales Person whose names contain the letter “N”.

Use of Wildcard Character in SUMIFS Function with Multiple Criteria in Excel

Let’s see the steps.

Steps:

  • First, select the cells where you want to calculate the Total Sales. Here, I selected cell G8.
  • Then, in cell G8 write the following formula.
=SUMIFS(D5:D14,C5:C14,G5,B5:B14,"*n*")

Here, in the SUMIFS function, I selected cell range D5:D14 as sum_range, C5:C14 as criteria_range1, G5 as criteria1, B5:B14 as criteria_range2, and “*n*” as criteria2. Here, “*n*” means any word that contains the letter “n”. Now, the formula will sum the values from the cell range D5:D14 that meet both criteria1 and criteria2.

  • After that, press ENTER to get the result.

Read More: SUMIFS with Wildcard in Excel (+ Alternative Formulas)


Things to Remember

  • Generally, the SUMIFS function works with AND logic. But, you can get results for OR logic with the SUMIFS function easily by following the methods from examples 5, 6, or 7.
  • If you are using an older version of Excel than Excel 2019 then you will have to press CTRL+SHIFT+ENTER to get results from an Array formula.

Practice Section

Here, I have provided a practice sheet for you to practice the explained examples of using the SUMIFS function in Excel for multiple criteria.

Practice Sheet for SUMIFS Function with Multiple Criteria


Conclusion

To conclude, I tried to explain the uses of the SUMIFS function in Excel with multiple criteria in this article. Here, I explain 8 different examples. I hope this article was helpful for you. For more articles stay connected with ExcelDemy. If you have any questions, feel free to let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo