“How to Use the Excel SUMIFS Function” article explains SUMIFS has limitations, i.e., unable to handle multiple OR criteria, either in the same or different columns.
There are data such as the image below.
The following are questions could not be solved by SUMIFS function
Multiple OR Criteria in the Same Column
How much are the total sales before July 2, 2018, and after July 4, 2018?
Solution #1, SUMIF Function, OR Function and Helper Column
This solution takes advantage of the helper column as a place to analyze all criteria using OR function.
Once all criteria have been analyzed, it’s easy for the SUMIF function to answer the question. Just looking for the TRUE value in the helper column and add up the sales data.
Here is the formula for the helper column
=OR(C2<DATEVALUE("7/2/2018"),C2>DATEVALUE("7/4/2018"))
* the DATEVALUE function converts text data into date data.
The result is as shown below
The value in the helper column will be TRUE if the sale date is before July 2, 2018, or after July 4, 2018.
The next step is easy. Just use the SUMIF function, helper column as range argument and TRUE as criteria argument.
Here is the formula.
=SUMIF(F2:F14,TRUE,E2:E14)
The result is 52.002
Solution #2, SUMPRODUCT Function, and Array Formula
This solution uses no helper column, but the array formula to analyze multiple OR criteria. Here is a formula using the SUMPRODUCT function and an array formula
=SUMPRODUCT((E2:E14)*((C2:C14<DATEVALUE("7/2/2018"))+(C2:C14>DATEVALUE("7/4/2018"))>0))
The result is as shown below
How does this formula work?
The core of the above formula is on the section
((C2:C14<DATEVALUE("7/2/2018"))+(C2:C14>DATEVALUE("7/4/2018"))>0)
This section analyzes 2 OR criteria. Let’s describe one by one.
Edit the formula above, block C2:C14<DATEVALUE(“7/2/2018”) section, then press the F9 button. The results are as shown below.
Appears 13 TRUE/FALSE values in a curly bracket, this section analyzes the first criteria, whether the sale date is smaller than July 2, 2018. There are two sales data both on July 1, 2018. See the image above, TRUE value position in accordance with the data position.
Block C2:C14>DATEVALUE(“7/4/2018”) section, then press the F9 button. The results are as shown below.
There are another 13 TRUE/FALSE values, this section analyzes the second criteria, whether the sale date is greater than July 4, 2018. There are five sales data; the TRUE value position corresponds to the data position.
Block the results of the first and second criteria; both are connected by the addition operator “+”, then press the F9 key.
The result is as shown below
Why the result turned into 13 numbers 0 and 1, as explained in the “How to Use OR Function” article, the TRUE/FALSE value will be converted to 1/0 if involved in mathematical operations. 26 TRUE/FALSE values above are involved in addition operation “+”.
Here are the illustrations
If the addition result is greater than 0, then one of the criteria is met, just as the results of the OR function.
The next step is to check whether the addition result is greater than 0. Block 13 numbers 0 and 1 in curly brackets and a “>0” sign.
The result is as shown below
Another 13 TRUE/FALSE values appear. Pay attention to the thirteen data; the TRUE value position is the same as the data position by sales date before July 2, 2018, or after July 4, 2018. The bottom line is this is the result of multiple OR criteria.
Block E2:E14 section, then press F9 key. The results are as shown below.
13 numbers appear, just like the numbers in the range E2: E14. Block all numbers and TRUE/FALSE values; both are connected by the multiplication operator “*”, then press the F9 key.
The result is as shown below,
As a result, 13 numbers partly remain unchanged and partially changed to 0.
Why would this happen? Like the previous explanation, the TRUE/FALSE value will be converted to 1/0 if involved in mathematical operations, the TRUE/FALSE values above involved in the multiplication operation.
Here’s an illustration.
The number 0 appears because the sale date does not meet the criteria, in other words, a non-zero number is sales before July 2, 2018, OR after July 4, 2018.
Finally, the SUMPRODUCT function will add up all the numbers. There is no need to press the CTRL + SHIFT + ENTER button to finish formula writing, because of the SUMPRODUCT function can handle array data.
For more details, please see the following video tutorial
Solution #3, SUM Function and Array Formula
This solution is similar to solution #2, but the SUMPRODUCT function is replaced by SUM function.
The formula
=SUM((E2:E14)*((C2:C14<DATEVALUE("7/2/2018"))+(C2:C14>DATEVALUE("7/4/2018"))>0))
SUM function unable to handle the array data, finish the formula writing by pressing the CTRL+SHIFT+ENTER button simultaneously.
Note: the array formula successfully created If there is a curly bracket at the beginning and the end of the formula.
For more details, please see the video tutorial below.
Another Question for Multiple OR Criteria in the Same Column
How much are the total sales of the “West” and “East” areas?
Solution #1, SUMIF Function, OR Function and Helper Column
The formula for the helper column.
=OR(A2="West",A2="East")
The formula for the total sales of the “West” and “East” areas.
=SUMIF(F2:F14,TRUE,E2:E14)
The result is as shown below
Solution #2, SUMPRODUCT Function, and Array Formula
The formula
=SUMPRODUCT((E2:E14)*((A2:A14="West")+(A2:A14="East")>0))
The result
Solution #3, SUM Function and Array Formula
The formula
=SUM((E2:E14)*((A2:A14="West")+(A2:A14="East")>0))
The result
Multiple OR Criteria in Different Column
How much are the total sales of “East” area OR “iPhone X”?
Weird question :D, but this question to show there is a solution for multiple OR criteria in a different column.
Solution #1, SUMIF Function, OR Function and Helper Column
The formula for the helper column
=OR(A2="East",B2="iPhone 6")
The formula for the total sales of the “East” area OR “iPhone X”
=SUMIF(F2:F14,TRUE,E2:E14)
For more details, please see the following video tutorial.
Solution #2, SUMPRODUCT Function, and Array Formula
=SUMPRODUCT((E2:E14)*((A2:A14="East")+(B2:B14="iPhone 6")>0))
How does the formula work? Please see the following video tutorial.
Solution #3, SUM Function and Array Formula
=SUM ((E2:E14)*((A2:A14="East")+(B2:B14="iPhone 6")>0))
How does the formula work? Please see the video tutorial below.
Which One is the Best Solution?
If the helper column does not matter for you, then solution #1 is the best, simple but solve the problem.
If you want no helper column solution, then the choice is to use an array formula. The next question uses the SUMPRODUCT function or the SUM function.
The SUMPRODUCT function by default can handle an array formula, so no need for CTRL+SHIFT+ENTER button to finish the formula. For me, this is better.
Which one is your choice?
There is another solution, simple with no helper column, i.e., use the solution #1. What about the helper column? Just hide it using “Hide column” menu :D, simple with no helper column, right? 😀
