ArrayFormula with SUMIFS not Working in Google Sheets

Question

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.

Answer 1

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_)))