In this tutorial, I am going to explain the use of OR condition in SUMPRODUCT in Google Sheets.
SUMPRODUCT in Google Sheets is very handy. We can deviate from its core use. The real purpose of SUMPRODUCT is to find the product of two arrays that also equal in size. But you can use this function in a single array also but that would act as COUNTIF. Quite intriguing, right?
Don’t worry! In this tutorial that related to the use of OR condition in SUMPRODUCT in Google Sheets, I’ve included answers to your all questions related to the same.
The OR Condition in SUMPRODUCT in Google Sheets
I know I should start with the basic. Some of you may already know the use of SUMPRODUCT. But for a quick reference, see the basic use of SUMPRODUCT below. Also, you can check my Google Sheets Functions Guide.
Basic Example to Google Sheets SUMPRODUCT Function
In this, the formula in C7 is the product of two equal sized arrays, i.e. B2: B4 and C2: C4. In other words, it’s the sum of the products in Column D.
=B2*C2+B3*C3+B4*C4
Is there any other formula to find the product of two equal sized arrays?
See this formula.
=ArrayFormula(SUM(B2:B4*C2:C4))
SUMPRODUCT in Single Column in Google Doc Sheets
In the beginning, I have told you we can use SUMPRODUCT in a single column and that acts as a COUNTIF, right?
=sumproduct(A2:A="Apple")
This formula counts all the occurrences of the text “Apple” in column A and that’s equal to the below COUNTIF formula.
=countif(A2:A,"Apple")
How to Use the OR Condition in SUMPRODUCT in Google Sheets
We can use OR Logic in SUMPRODUCT to conditionally count or find the product. But we can’t use the OR Logical function directly in SUMPRODUCT. We can instead use the “+” sign as its equivalent.
The use of OR in SUMPRODUCT is different when the criteria are in the same column or in a different column/columns.
Let’s see how to use OR condition in SUMPRODUCT in Google Sheets in the same column first.
The OR in SUMPRODUCT in the Same Column in Sheets
Count Colum A if the value is either “Apple” or “Mango”.
=SUMPRODUCT((A2:A="Apple")+(A2:A="Orange"))
Now to find the product of these two items, you can use the formula as below.
=SUMPRODUCT(((A2:A="Apple")+(A2:A="Orange"))*B2:B*C2:C)
The OR in SUMPRODUCT in Two Different Columns in Google Sheets
In the just above example, I’ve detailed how to use the OR in SUMPRODUCT in the same column in Google Sheets. Now see what happens when the columns are different.
It’s important to know how to use OR in two different columns in Sumproduct.
In the below example I want to count all the instances of “Crusher 1” in column B or “Gravel” in Column C.
The formula counts all the cells containing the text “Crusher 1” in Column B. If the text is different then obviously it checks the texts in Column C for the text “Gravel” and counts it.
You can see the formula applied in Cell F3. How this formula differs from the previous one. Here I’ve wrapped the criteria with the function SIGN. Why? I will come to that. Before see how to find the product based on the OR in two different columns.
=Sumproduct(SIGN((B2:B7="Crusher 1")+(C2:C7="Gravel"))*D2:D7*E2:E7)
Now I will explain why should we use the SIGN function when we use OR condition in two different columns in Sumproduct in Google Sheets.
The Sumproduct OR Formula Logic
This is the criteria part in the above two formulas.
=(B2:B7="Crusher 1")+(C2:C7="Gravel")
Just apply this formula in any cell and see what this formula returns. It probably would return the value 0! Because Google Sheets won’t execute this formula as the cell references used are Arrays.
SUMPRODUCT is an array formula. So it works in SUMPRODUCT. But if you individually use this formula, you should include ArrayFormula with it. See this.
For example, in the first row, it works like;
=(B2="Crusher 1")+(C2="Gravel")
i.e, TRUE+FALSE
The Boolean TRUE has the value of 1 and FALSE has the value of 0. So it returns 1.
The SUMPRODUCT finds the product as below.
Column D * Column E * the newly generated array
See the value in F4 which is 2. It causes wrong output in the SUMPRODUCT. We should convert this number to 1. The SIGN function does this.
That’s all about how to use OR condition in SUMPRODUCT in Google Sheets. Enjoy!
Related Reading:
1. How to Do a Case Sensitive Sumproduct in Google Sheets.
2. The Chart to Learn Text, Date, Numeric Criteria in Sumproduct Function in Google Sheets.
3. Compare Sumifs, Sumproduct, Dsum with Example in Google Sheets.
4. How to Use Date Difference as Criteria in SUMPRODUCT in Google Sheets.
