I'm encountering a challenge with an ArrayFormula in Google Sheets that utilizes the SUMIFS function. Here's the scenario: I have a report with data in columns SpoPartnerShare, SpoMonth, and SpoPartnerId in the sheet (Spo), along with another sheet (Sum) containing a dropdown menu in cell C1 for selecting the "Reporting Month." Additionally, in sheet Sum, I have the SumPartnerId. The following single-cell formula works flawlessly when copied down a column:
=SUMIFS(SpoPartnerShare, SpoMonth, $C$1, SpoPartnerId, A2)
This formula leverages the value in C1 (Reporting Month) to filter SpoMonth and then sums SpoPartnerShare based on matching SumPartnerId in column A (A2 or A50 depending on the formula's placement). The Problem: When attempting to convert this to an ArrayFormula as shown below:
=ARRAYFORMULA(SUMIFS(SpoPartnerShare, SpoMonth, SumRepMonth, SpoPartnerId, SumPartnerId))
I only see zeros. Despite the logic appearing consistent, the ArrayFormula isn't producing the expected results. Request for Assistance: I am seeking guidance on fixing the ArrayFormula or exploring alternative approaches to achieve the desired outcome. I have tried various AI chats, forums, and solutions without success, investing significant time into troubleshooting this issue.
SUMIFS does not work nicely with ARRAYFORMULA, you can concatenate the conditions and use SUMIF:
=ARRAYFORMULA(SUMIF(SumRepMonth&SumPartnerId,C1&SpoPartnerId,SpoPartnerShare))
Or use the MAP function:
=MAP(SumPartnerId,
LAMBDA(SumPartnerId_,
SUMIFS(SpoPartnerShare,SpoMonth,C1,SpoPartnerId,SumPartnerId_)))