In this tutorial, we will explore the use of the OR condition in the SUMPRODUCT function in Google Sheets. SUMPRODUCT is a powerful tool that can be used to find the product of two arrays of equal size. However, it can also be used in a single array to act as a COUNTIF function. This flexibility makes SUMPRODUCT a must-know function for any Google Sheets user.
The OR Condition in SUMPRODUCT in Google Sheets
Before we dive into the details of using the OR condition in SUMPRODUCT, let’s quickly review the basic use of the function. In its simplest form, SUMPRODUCT calculates the sum of the products in a given range. For example, if we have two arrays, B2:B4 and C2:C4, we can use the formula =B2*C2+B3*C3+B4*C4 to find the product of these arrays. Alternatively, we can use the formula =ArrayFormula(SUM(B2:B4*C2:C4)) to achieve the same result.
SUMPRODUCT in Single Column in Google Sheets
One interesting feature of SUMPRODUCT is that it can be used in a single column to count occurrences of a specific value. This is equivalent to using the COUNTIF function. For example, the formula =SUMPRODUCT(A2:A="Apple") counts all the occurrences of the text “Apple” in column A, which is the same as =COUNTIF(A2:A,"Apple").
How to Use the OR Condition in SUMPRODUCT in Google Sheets
Now let’s get to the main topic of this tutorial: using the OR condition in SUMPRODUCT. We can use OR logic in SUMPRODUCT to conditionally count or find the product. However, we can’t use the OR function directly in SUMPRODUCT. Instead, we can use the “+” sign as its equivalent.
The OR in SUMPRODUCT in the Same Column in Sheets
To count the occurrences of multiple values in the same column, we can use the OR condition in SUMPRODUCT. For example, if we want to count the occurrences of “Apple” or “Mango” in column A, we can use the formula =SUMPRODUCT((A2:A="Apple")+(A2:A="Mango")). To find the product of these values, we can extend the formula to include the corresponding values in columns B and C, like this: =SUMPRODUCT(((A2:A="Apple")+(A2:A="Mango"))*B2:B*C2:C).
The OR in SUMPRODUCT in Two Different Columns in Google Sheets
Using the OR condition in SUMPRODUCT becomes slightly more complicated when the criteria are in different columns. To count all the instances of “Crusher 1” in column B or “Gravel” in column C, we can use the formula =SUMPRODUCT(SIGN((B2:B7="Crusher 1")+(C2:C7="Gravel"))*D2:D7*E2:E7). Notice that we’ve wrapped the criteria with the SIGN function. This is necessary because using the OR condition directly in the formula won’t work as expected. The SIGN function ensures that the OR condition evaluates to either 0 or 1, which is essential for the SUMPRODUCT calculation.
In summary, using the OR condition in SUMPRODUCT can help us count or find the product of multiple values in Google Sheets. By understanding how to apply this logic in single columns and across different columns, we can unlock the full potential of the SUMPRODUCT function.
To learn more about Google Sheets functions and explore other advanced techniques, visit Crawlan.com.
P.S. If you’re interested in related topics, check out these articles:
