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.

### 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**.

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

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.

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.

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**

**Excel SUMIFS with Multiple Sum Ranges and Multiple Criteria****Excel SUMIFS with Multiple Vertical and Horizontal Criteria****How to Use VBA Sumifs with Multiple Criteria in Same Column****SUMIFS with INDEX-MATCH Formula Including Multiple Criteria**

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

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

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

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

- 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”**.

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

## 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

**How to Apply SUMIFS with INDEX MATCH for Multiple Columns and Rows****Exclude Multiple Criteria in Same Column with SUMIFS Function****How to Use SUMIFS When Cells Are Not Equal to Multiple Text****SUMIFS with Multiple Criteria Along Column and Row in Excel****SUMIF with Multiple Criteria in Column & Row in Excel (Both OR and AND Type)**