For example, there are data such as the image below.

How does the SUMIF function answer the following multiple criteria questions, either in the same or different columns, either with AND, OR operator or both?

**SUMIF Multiple Criteria Same Column (AND Operator)**

### The Question

How much is the total sales for July 2, 2018, until July 4, 2018?

### The Criteria

- Criteria #1, sale date >= July 2, 2018
- Criteria #2, sale date <= July 4, 2018

Both criteria must be met (AND Criteria)

### The Formula

The formula for helper column

=AND(C2>=DATEVALUE("7/2/2018"),C2<=DATEVALUE("7/4/2018"))

The formula for total sales

=SUMIF(F2:F14,TRUE,E2:E14)

### The Result

### Another Solution

You can use the SUMIFS function to answer this question. The video tutorial below explains how to use the SUMIFS function to answer the questions above.

**SUMIF Multiple Criteria Same Column (OR Operator)**

### The Question

How much are the total sales before July 2, 2018, and after July 4, 2018?

### The Criteria

- Criteria #1, sale date < July 2, 2018
- Criteria #2, sale date > July 4, 2018

Only one criterion must be met (OR Criteria)

### The Formula

The formula for helper column

=OR(C2<DATEVALUE("7/2/2018"),C2>DATEVALUE("7/4/2018"))

The formula for total sales

=SUMIF(F2:F14,TRUE,E2:E14)

### The Result

### Another Solution

You can use the SUMPRODUCT function and array formula to solve this question.

An alternative to the SUMPRODUCT function is to use the SUM function and array formulas.

**SUMIF Multiple Criteria Different Column (AND Operator)**

### The Question

How much are the total sales of the iPhone X in West area?

### The Criteria

- Criteria #1, Area = “West”
- Criteria #2, iPhone variant = “iPhone X”

Both criteria must be met (AND Criteria) and are in different columns.

### The Formula

The formula for helper column

=AND(A2="West",B2="iPhone X")

The formula for total sales

=SUMIF(F2:F14,TRUE,E2:E14)

### The Result

### Another Solution

The SUMIFS function answer this question easily. The following video tutorial explains how to use the SUMIFS function.

**SUMIF Multiple Criteria Different Column (OR Operator)**

### The Question

How much are the total sales of “East” area OR “iPhone 6”?

Strange question :D, but there is a solution for multiple OR criteria questions in a different column.

### The Criteria

- Criteria #1, Area = “East”
- Criteria #2, iPhone variant = “iPhone 6”

Both criteria do not have to be met all (OR Criteria) and are in different columns if one of the criteria is met then EXCEL will calculate the sales data.

### The Formula

The formula for helper column

=OR(A2="East",B2="iPhone 6")

The formula for total sales

=SUMIF(F2:F14,TRUE,E2:E14)

### The Result

### Another Solution

The SUM function and array formula can solve this question.

Use the SUMPRODUCT function and array formula for other alternatives.

**SUMIF Multiple Criteria Different Column (AND/OR Operator)**

### The Question

How much is the total sales for July 2, 2018, until July 4, 2018, in the West and East Area?

### The Criteria

- Criteria #1, sale date >= July 2, 2018
- Criteria #2, sale date <= July 4, 2018
- Criteria #3, area = “West”
- Criteria #4, area = “East”

Criteria #1 and criteria #2 must be met all (AND Criteria), criteria #3 and criteria #4 do not have to be fulfilled all (OR Criteria). Both groups of criteria must be TRUE so that the sales data is calculated.

### The Formula

Formula for helper column

=AND(AND(C2>=DATEVALUE("7/2/2018"),C2<=DATEVALUE("7/4/2018")),OR(A2="West",A2="East"))

Formula for total sales

=SUMIF(F2:F14,TRUE,E2:E14)

### The Result

### Another Solution

Please use array formula for no helper column solution, using the SUMPRODUCT function or the SUM function is the same result.

The difference, the SUM function cannot handle array formulas by default. If you use the SUM function, end formula writing by pressing CTRL + SHIFT + ENTER key simultaneously

#### SUMPRODUCT Function and Array Formula

**The Formula**

=SUMPRODUCT((E2:E14)*(((C2:C14>=DATEVALUE("7/2/2018"))*(C2:C14<=DATEVALUE("7/4/2018"))=1)*((A2:A14="West")+(A2:A14="East")>0)))

**The Result**

#### SUM Function and Array Formula

**The Formula**

=SUM((E2:E14)*(((C2:C14>=DATEVALUE("7/2/2018"))*(C2:C14<=DATEVALUE("7/4/2018"))=1)*((A2:A14="West")+(A2:A14="East")>0)))

**The Result**

**The Conclusion**

- The formula for total sales in all questions is the same; the difference is the formula in the helper column, all of them use logical functions adjusted to a variety of questions
- Another solution for multiple AND criteria is SUMIFS function
- Another solution for multiple OR criteria is an array formula