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
