While working with large amounts of data in Excel, sometimes you need to sum a column with criteria. In order to sum with criteria, we use the **SUMIF** function. Now, if you find yourself in a situation where you need to sum a column or row with multiple criteria, you can also use the **SUMIF** function. In this tutorial, we are going to show you how to use the **SUMIF** function with multiple criteria in Excel.

**Table of Contents**hide

## Download Practice Workbook

Download this practice workbook.

## Overview of the SUMIF Function

**Objective:**

The **SUMIF** function sums the values in a range that meets the criteria that you specify. We Use the **SUMIF** function in Excel to sum cells based on numbers that meet specific criteria.

**Syntax of the SUMIF Function:**

**=SUMIF(range, criteria, [sum_range])**

**Arguments:**

**range: **The range of cells that you want to be evaluated by criteria. Cells in each range must be numbers or names, arrays, or references that contain numbers.

**criteria: **The criteria in the form of a number, expression, a cell reference, text, or a function that defines which cells will be added.

**sum_range:** The actual cells to add if you want to sum cells other than those defined in the range argument. If the sum_range argument is dropped, Excel adds the cells that are specified in the range argument (the same cells to which the criteria is applied).

## 3 Effective Methods to Use SUMIF with Multiple Criteria in Excel

Now, you canâ€™t directly use the **SUMIF** function with multiple criteria. Basically, the **SUMIF** function takes only a single condition. But, we can use the **SUMIF** function with other functions or methods to solve our problem.

To demonstrate this tutorial, we are going to use the following dataset:

We are going to find total sales based on some criteria using the **SUMIF** function.

In order to use the **SUMIF** function with multiple criteria, we are providing you with 3 methods that can truly solve your problem.

### 1. Adding Multiple SUMIF Functions in Excel

Now, the first one is pretty simple to use. As we know, the **SUMIF** function only takes a single condition. So, we can add multiple **SUMIF** functions with different conditions. It means we are summing up with multiple criteria.

**The Generic Formula:**

**=SUMIF(range,criteria,sum_range)+SUMIF(range,criteria,sum_range)+â€¦â€¦..**

*Now, we are going to find the Total Sales of John and Stuart in January.*

ðŸ“Œ** Steps**

1. Type the following formula in **Cell I5**:

`=SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H6,D5:D12)`

2. Then, press **Enter.**

As you can see, we have successfully found the Total Sales of **John** and **Stuart** in **January**. You can add more criteria with this method. And easily find the Total Sales of any person or any Product.

ðŸ”Ž **Breakdown of the Formula**

**âž¤**** SUMIF(C5:C12,H5,D5:D12)**

This function sums up **Johnâ€™s **sale in **January **and returns **1200**.

**âž¤**** SUMIF(C5:C12,H6,D5:D12)**

This function sums up **Stuartâ€™s **sale in January and returns **700**.

**âž¤**** SUMIF(C5:C12,H5,D5:D12)+SUMIF(C5:C12,H6,D5:D12)**

Finally, our formula adds those values and returns **$1900.**

### 2. Using the SUMIF and SUMPRODUCT Functions with Multiple Criteria in Excel

Now, another way is to use the **SUMIF** function with multiple criteria is using it with the **SUMORODUCT **function.

The **SUMPRODUCT **function returns the sum of the products of similar ranges or arrays. The default method is multiplication, but you can also add, subtract and divide with this function.

Now, we will use the multiple criteria as a range in the **SUMIF** function. After that, it will return an array. Then, our **SUMPRODUCT** function will add those arrays and return results.

**The Generic Formula:**

**=SUMPRODUCT(SUMIF(range,criteria_range,sum_range))**

*We want to find the Total Sales of all the salespeople in March.*

ðŸ“Œ **Steps**

1. Type the following formula in **Cell J5**:

`=SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))`

2. Then, press **Enter.**

As you can see, we are successful in finding the total sales of all the salespeople in **March**. In our opinion, this formula is more efficient than the previous one if your criteria come from a particular column. You can easily pass those criteria as a range in the **SUMIF **function.

ðŸ”Ž** Breakdown of the Formula**

**âž¤**** SUMIF(C5:C12,I5:I7,F5:F12)**

This function will return an array: **{1300;700;1200}**

**âž¤**** SUMPRODUCT(SUMIF(C5:C12,I5:I7,F5:F12))**

Finally, SUMPRODUCT will add those arrays and return **$3200.**

### 3. Using the SUM and SUMIF Functions with Multiple Criteria

Now, by combining the **SUM **and **SUMIF **functions we can perform this with multiple criteria. This function is almost similar to the 1st method. The basic difference is we are using the **SUM** function instead of the plus sign(+).

**The Generic Formula:**

**=SUM(SUMIF(range,criteria2,sum_range1),SUMIF(range,criteria2,sum_range2)â€¦â€¦.)**

*We want to find the total sales of the product Book in February and total sales of the product Pen in January.*

ðŸ“Œ **Steps**

1. Type the following formula in **Cell I5**:

`=SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))`

2. Then, press **Enter.**

Finally, you can see we are successful in finding the total sales of the product **Book **in **February **and total sales of the product **Pen **in **January**.

ðŸ”Ž** Breakdown of the Formula**

**âž¤**** SUMIF(B5:B12,H5,E5:E12)**

This function will return **1000**.

**âž¤**** SUMIF(B5:B12,H6,D5:D12)**

This function will return **1400**.

**âž¤**** SUM(SUMIF(B5:B12,H5,E5:E12),SUMIF(B5:B12,H6,D5:D12))**

Finally, the **SUM **function will add those values and return **$2400.**

### 4. Using the SUMIFS Function in Excel (Bonus)

Now, this part is a bonus for you. In order to sum up multiple criteria, you can use the **SUMIFS** function. This function takes multiple criteria. If you donâ€™t want to use complex methods or formulas, this function will work at ease. This function sum cells that meet multiple criteria.

**The Basic Syntax of the SUMIFS Function:**

**=**

**SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], â€¦)**

**sum_range**: This field is required. This is the range of cells to sum.

**criteria_range: **Required. We will use this to test using **criteria1.**

**criteria1: **Required. The criteria that specify which cells in **criteria_range1** will be summed.

**criteria_range2, criteria2: **This is optional. Further ranges and their associated criteria. You can enter up to 127 range/criteria combinations.

*We want to find the total sales of Stuart in January greater than $150.*

ðŸ“Œ **Steps**

1. Type the following formula in **Cell I7**:

`=SUMIFS(D5:D12,C5:C12,I4,D5:D12,">150")`

2. Then, press **Enter.**

In the end, we are successful in finding the total sales of **Stuart **in **January **greater than** $150.**

## ðŸ’¬ **Things to Remember**

âœŽ If you are working with a small dataset and single criteria then the **SUMIF** function will be efficient.

âœŽ If you are working with a large dataset and complex criteria, using the **SUMIFS** function may be the optimal way.

## Conclusion

To conclude, I hope you found this tutorial helpful to use **SUMIF** with multiple criteria. We recommend you learn and apply all these methods to your dataset. Definitely, it will improve your knowledge. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this. Donâ€™t forget to check our website **Exceldemy.com** for various Excel-related problems and solutions.