In this juicy tutorial, we’ll unveil the secret of using wildcards in the powerful Sumproduct function in Google Sheets. You’ll learn alternatives to the traditional tilde, asterisk, and question mark wildcards, and how to apply them to supercharge your spreadsheet skills. So grab your favorite snack and let’s dive in!
Unleashing Wildcard Magic in Sumproduct
Currently, Google Sheets doesn’t directly support wildcards in the Sumproduct function. However, fear not! We have some brilliant workarounds using the Find/Search or Regexmatch functions within Sumproduct.
Why Choose Regexmatch?
Among these options, I recommend using Regexmatch. It has the ability to replace all three wildcard characters – tilde (~), asterisk (*), and question mark (?) in Sumproduct. On the other hand, Find/Search can only provide partial match functionality.
Most users who have transitioned from Excel to Google Sheets find Sumproduct highly useful, despite there being alternative functions like Sumif, Countif, and Query. Perhaps it’s the array support in Excel that has won them over.
While Google Sheets offers great flexibility in array utilization, Excel has been catching up lately. However, there are still some exceptional cases where Excel outshines Google Sheets.
The Power of Regexmatch and Search/Find Functions
Now, let’s focus on how to use wildcards with the Sumproduct function in Google Sheets. If you only need partial match capabilities (where the wildcard is used on both sides of a criterion, e.g., TV), you can rely on the Search or Find functions.
The Search function is not case-sensitive, while Find is case-sensitive. In addition, you might want to use the Isnumber function in conjunction with these functions. We’ll explore this further in the examples below.
But wait, there’s something even better – Regexmatch! This function can handle all three wildcards in Sumproduct. So let’s roll up our sleeves and get started with an example using Search and Isnumber.
Example: Utilizing Search or Find Function
Suppose we want to sum the “qty” column when the “item” is “TV” (using the criterion TV). Here’s the formula we can use:
=SUMPRODUCT(ISNUMBER(SEARCH("TV", C2:C11)), D2:D11)
Let’s break down the formula:
- Array 1:
ISNUMBER(SEARCH("TV", C2:C11)) - Array 2:
D2:D11 - Syntax:
SUMPRODUCT(array1, [array2, …])
The Search formula in Array 1 returns 1 (or the first position of the string “TV”) in rows where there’s a partial or full match with “TV”. The Isnumber function then converts the returned numbers to TRUE.
Make sure to keep cell range E2:E11 empty, then try the Array 1 formula mentioned above in cell E2. However, remember that it will only work for the current row. To make it work for the entire range, use =ArrayFormula(ISNUMBER(SEARCH("TV", C2:C11))). Fortunately, within Sumproduct, we don’t need the ArrayFormula function.
But why do we need the Isnumber function? There are two reasons:
- If the position of the criterion “TV” is not at the beginning, Isnumber may return a number other than 1. This could lead to an incorrect sum in column D. The formula would provide the product instead.
- If there is no match, Search may return #VALUE!, causing Sumproduct to also return the same error.
Embracing the Full Power of Wildcards Using Regexmatch
Let’s continue exploring the dataset in cell range A1:D (you’ll find the sample sheet link at the end of this tutorial). In the following examples, we’ll show you how to replace the traditional wildcard characters with Regexmatch in Sumproduct.
-
Single Character Search:
=SUMPRODUCT(REGEXMATCH(C2:C11,"TV."), D2:D11) -
Multiple Character Search:
=SUMPRODUCT(REGEXMATCH(C2:C11,"TV*"), D2:D11) -
Literal Character Search:
=SUMPRODUCT(REGEXMATCH(C2:C11,"TV~X"), D2:D11) -
Multiple Criteria Search (OR Logic):
=SUMPRODUCT(REGEXMATCH(C2:C11,"TV|Radio"), D2:D11) -
Multiple Criteria Search (AND Logic):
=SUMPRODUCT(REGEXMATCH(C2:C11,"(?=.*TV)(?=.*Radio)"), D2:D11) -
Case-Sensitive Search:
=SUMPRODUCT(REGEXMATCH(C2:C11,"(?i)tv"), D2:D11) -
Simple Wildcard Search without Regexmatch:
=SUMPRODUCT(B2:B11="No?th", D2:D11)
In the seventh example, Regexmatch isn’t necessary. We can replace that formula with the following one: =SUMPRODUCT(B2:B11="No?th", D2:D11).
Note: If you try to use the “Array 1” formulas outside of Sumproduct, make sure to include the ArrayFormula function.
That’s all you need to know about using wildcards in the Sumproduct function in Google Sheets. Now, go ahead and apply this newfound knowledge to impress your friends and colleagues with your spreadsheet prowess!
Thanks for joining us on this exciting adventure. Remember, for more amazing Google Sheets tips, tricks, and tutorials, head over to Crawlan.com. Enjoy your journey towards spreadsheet mastery!
Resources:
