Search code examples
google-sheetsarray-formulassumifs

SUMIFS with Arrayformula in Google Sheets from multiple array

https://docs.google.com/spreadsheets/d/1JvZerzOiW0vdewnfZT47WuU3TPlW15UHUSpvII1HE50/edit#gid=0

I want to use an arrayformula array function with one formula in cell N4 to get the q'ty values in that table with SUMIFS.

i did like this =ArrayFormula(sumifs($E$4:$E$29,$B$4:$B$29,N3:S3,$D$4:$D$29,M4:M20,$C$4:$C$29,L4:L20))

But I think I got the wrong answer.

Solution

  • I recommend using QUERY as suggested by @doubleunary, but if you want to do this with SUMIFS you can use the MAP function:

    =MAP(N3:S3,LAMBDA(m,MAP(L4:L20,M4:M20,LAMBDA(s,i,SUMIFS(E4:E,B4:B,m,C4:C,s,D4:D,i)))))

    Or you can use SUMIF with concatenated criteria and criteria range:

    =ARRAYFORMULA(SUMIF(B4:B&C4:C&D4:D,N3:S3&L4:L20&M4:M20,E4:E))

    EDIT: Solution with MAKEARRAY

    =MAKEARRAY(ROWS(L4:M20),COLUMNS(N3:S3),LAMBDA(r,c,SUMIFS(E4:E,B4:B,INDEX(N3:S3,,c),C4:C,INDEX(L4:L20,r),D4:D,INDEX(M4:M20,r))))