SUMIFS with OR Condition in Google Sheets: A Sneaky Hack for Effective Data Analysis

Google Sheets is a powerful tool for data analysis, but it has its own unique way of applying SUMIFS with an OR condition (logic). If you’re familiar with Excel, you may already know the array constant method for achieving this; however, that won’t work in Google Sheets. Don’t worry, though! I’m here to share a sneaky hack that will make your data analysis a breeze.

The Syntax of the SUMIFS Function

Let’s start with a quick recap of the syntax of the SUMIFS function in Google Sheets:

SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, …], [criterion2, …])

In this formula:

• sum_range refers to the range you want to sum.
• criteria_range1 represents the first range you want to apply the criteria to.
• criterion1 is the condition you want to apply to criteria_range1.
• You can have multiple criteria_range and criterion pairs to specify additional conditions.

Now, let’s dive into the hack that will allow you to use the OR condition in your SUMIFS formulas.

The REGEXMATCH Approach

To achieve the OR condition in Google Sheets, you need to use the REGEXMATCH function or one of the Lambda helper functions. In this article, I’ll focus on the REGEXMATCH function. Here’s how it works:

1. Specify the conditions you want to apply in the status column. For example, let’s say you want to sum the delivered quantities if the date of delivery is October 21, 2023, and the status of the delivery is either “sent” or “delivered.”

2. Use the REGEXMATCH function to check if the status contains either “sent” or “delivered”. Here’s an example of the formula:

    =ARRAYFORMULA(REGEXMATCH(D2:D14,"Sent|Delivered"))

   This formula will return an array of Boolean values. If the status in the cell contains “Sent” or “Delivered,” the corresponding element in the array will be TRUE, indicating a match. Otherwise, it will be FALSE.

3. Finally, use the SUMIFS function with the array generated by the REGEXMATCH formula. Here’s an example:

    =SUM(SUMIFS(C2:C14,A2:A14,DATE(2023,10,21),E2:E14,TRUE))

   In this formula, we’ve replaced the status column, which was originally in the D2:D14 criteria range, with the result of the REGEXMATCH formula.

Note: You don’t necessarily need to use a helper column (E2:E14) for the REGEXMATCH formula. In the SUMIFS function, you can replace that range with the REGEXMATCH formula itself.

The Lambda Approach

Alternatively, you can use the Lambda helper functions to handle the OR condition in your SUMIFS formulas. This approach is particularly useful if you’re working with large datasets.

Here’s an example of how to use the MAP function with Lambda:

=SUM(MAP({"Sent","Delivered"},LAMBDA(row, SUMIFS(C2:C14,A2:A14,DATE(2023,10,21),D2:D14,row))))

In this formula, we utilize the MAP function to iterate over the array {“Sent”,”Delivered”} and apply the lambda function to each element within this array. The lambda function, defined as LAMBDA(row, SUMIFS(C2:C14, A2:A14, DATE(2023, 10, 21), D2:D14, row)), is a crucial part of the formula.

The row parameter serves as the argument passed to the lambda function, representing either “Sent” or “Delivered.” The MAP function executes the lambda function twice, once with “Sent” and once with “Delivered” as the row argument, resulting in two values. The SUM function then aggregates the returned values, giving us the final sum of the results.

Conclusion

Congratulations! You now have the secret sauce for using the OR condition in your SUMIFS formulas in Google Sheets. Whether you choose to use the REGEXMATCH function or the Lambda approach, you’ll be able to perform effective data analysis with ease.

Don’t forget to check out the Crawlan.com website for more SEO tips and tricks. Stay tuned for more juicy secrets to help you conquer the world of data analysis!

Thanks for reading, my fellow data warriors – until next time!