If you heavily rely on SUMIFS to conditionally sum columns, you should learn how to use REGEXMATCH in SUMIFS. SUMIFS is not the only function that can perform multiple conditional sums. Functions like QUERY and SUMPRODUCT can also do the job. However, I know that some of you are not familiar with these two functions.
Multiple Conditions in the Same Column in Conditional Sum in Google Sheets
To sum based on multiple conditions in the same column in Google Sheets, you can use the SUMIF function with the ARRAYFORMULA function. For example, the following formula will sum the values in column C where the Priority in column B is either “High” or “Moderate”:
=ARRAYFORMULA( SUM( SUMIF(B2:B,{"High";"Moderate"},C2:C) ) ) Note: You can replace {“High”;”Moderate”} with VSTACK(“High”,”Moderate”) or HSTACK(“High”,”Moderate”).
The SUMIF function sums the values in a range based on a single criterion. The ARRAYFORMULA function allows you to apply the SUMIF function to an array of values, which is necessary when you are summing multiple criteria in the same column. The output will be in multiple cells, which the SUM function will aggregate.
Multiple Criteria in the Same Column and Another Column in Conditional SUM
In this example, you want to sum the O/S Amt (column C) for the Customer (column A) “Info Inspired” if the Priority (column B) is “High” or “Moderate.” SUMIF is not a good solution here because it would become complex. SUMIFS supports multiple criteria in conditional sums, but it does not support the curly bracket or VSTACK/HSTACK approach similar to SUMIF. This means that you cannot include multiple criteria inside curly brackets or VSTACK/HSTACK in SUMIFS. One solution is to use multiple SUMIFS formulas, but this is not recommended. A better solution is to use REGEXMATCH in SUMIFS.
How to Use REGEXMATCH in SUMIFS to Handle Multiple Criteria Columns
To use REGEXMATCH in SUMIFS to handle multiple criteria columns, you can use the following steps:
- Create a regular expression that matches the conditions for the column that you want to filter. To do this, simply list your criteria separated by pipes (|). For example, the regular expression “High|Moderate” will match any value in the column that is equal to “High” or “Moderate”.
- Use the REGEXMATCH function to test the values in the column against the regular expression. The syntax for the REGEXMATCH function is REGEXMATCH(text, regular_expression). For example, the following formula will return TRUE if the value in cell B2 matches the regular expression “High|Moderate”:
=REGEXMATCH(B2, "High|Moderate")- Use the SUMIFS function to sum the values in the column that you want to sum, based on the results (Boolean TRUE / FALSE) of the REGEXMATCH function. The syntax for the SUMIFS function is SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], …).
For example, the following formula will sum the values in column C for the Customer “Info Inspired” if the Priority in column B is “High” or “Moderate”:
=ARRAYFORMULA( SUMIFS( C2:C9, A2:A9, "Info Inspired", REGEXMATCH(B2:B9,"High|Moderate"), TRUE ) )REGEXMATCH in SUMIFS: Formula Explanation
In the above formula, the arguments used are as follows:
- Sum_range: C2:C9
- Criteria_range1: A2:A9
- Criterion1: “Info Inspired”
- Criteria_range2: REGEXMATCH(B2:B9, “High|Moderate”)
- Criterion2: TRUE
Explanation:
We need to use two conditions in column B. The SUMIFS function does not support multiple criteria in the same column. So we can’t use B2:B9 twice. To address this limitation, we can use the REGEXMATCH function. It will match both the criterion in column B and return an array with TRUE/FALSE values. So the criteria range will be the REGEXMATCH returned Boolean values and the criterion will be TRUE.
Here is another formula where we use three conditions in the same column using REGEXMATCH in SUMIFS:
=ARRAYFORMULA( SUMIFS( C2:C9, A2:A9, "Info Inspired", REGEXMATCH(B2:B9,"High|Moderate|Low"), TRUE ) )That’s all! This way, you can use REGEXMATCH in SUMIFS in Google Sheets to sum values based on multiple criteria in the same column.
Share this tip!
