To create a SUMIFS formula that expands its results, you’ll need to utilize a Lambda function in Google Sheets. While SUMIF can expand results using the ARRAYFORMULA function, SUMIFS cannot. However, there are alternative solutions to this issue that I will cover in this tutorial.
SUMIFS Array Formula for Expanded Results in Google Sheets
Let’s begin with an example that demonstrates the use of a SUMIFS array formula in Google Sheets. Suppose you have a dataset with delivery dates, quantities, and delivery confirmations. You want to calculate the total confirmed deliveries between two specific dates and with a confirmation mark.
To achieve this, you can use the following non-array SUMIFS formula:
=SUMIFS(B2:B14,A2:A14,">="&D2,A2:A14,"<="&E2,C2:C14,F2)However, to expand the results in cells, you’ll need to use a SUMIFS array formula. Although Microsoft 365 Excel supports expanding formulas, Google Sheets requires a different approach. Here’s how to use a SUMIFS array formula to expand its results:
SUMIFS with MAP Lambda for Expanding Array Results
To convert a non-array SUMIFS formula into a SUMIFS array formula using the MAP lambda function, follow these steps:
- Specify the criteria ranges individually within MAP.
- Name the criteria ranges within the Lambda function.
- Replace the existing criteria in the SUMIFS formula with the assigned names.
Here is the resulting SUMIFS array formula:
=MAP(D2:D3,E2:E3,F2:F3, LAMBDA(criterion1, criterion2, criterion3, SUMIFS(B2:B14,A2:A14,">="&criterion1,A2:A14,"<="&criterion2,C2:C14,criterion3)))This formula applies the LAMBDA function for each set of criteria provided in D2:D3, E2:E3, and F2:F3, resulting in a list of sums based on these different sets of criteria.
SUMIFS Array Formula Alternatives
Although the SUMIFS array formula is the recommended approach in most cases, there are alternative formulas you can consider in specific scenarios.
SUMIF (Criteria in Single Column)
If you want to apply multiple criteria within a single column, you can use the SUMIF function. Here is the syntax:
SUMIF(range, criterion, [sum_range])For example, to sum sales in Q1 and Q2 based on specified quarters and sales amounts, you can use the following formula:
=ARRAYFORMULA(SUMIF(A2:A9,E3:E4,C2:C9))SUMIF (Criteria in Multiple Columns)
In cases where you have multiple criteria across different columns, you can still use SUMIF to achieve the desired results. Here’s an example:
=ARRAYFORMULA(SUMIF(A2:A&B2:B,E3:E4&F3:F4,C2:C))Note that this alternative may not work in all scenarios involving comparisons.
Using QUERY as an Alternative to SUMIFS for Expanding Array Results
QUERY is a powerful function for data manipulation and visualization. You can use it as an alternative to SUMIFS for expanding array results. Here’s an example using fruit data:
=QUERY(A2:C,"Select A,B, sum(C) where A Matches 'January' and B matches 'Apple|Orange' group by A,B label sum(C)''")Please note that this formula is case-sensitive and the criteria are hard-coded.
Conclusion
In certain scenarios, you can also use the ARRAYFORMULA function to expand a function used within SUMIFS, especially for date-related functions. Remember to adapt the formulas to your specific dataset and requirements.
For more expert tips and tutorials on Google Sheets, visit Crawlan.com.
