Reputation: 537
I have been trying to Convert the SUMIFS formula into Array but its not working in google sheets then i tried to follow online instructions and i am unable to that.
I hope someone can help me to fix the fomrula:
Formula1:
SUMIFS('Employee Leave Setup'!$I:$I,'Employee Leave Setup'!$B:$B,$B2:B,'Employee Leave Setup'!$X:$X,K$1)
Formula2:
=ArrayFormula(if(len(B2:B), iferror(vlookup(B2:B,QUERY({'Employee Leave Setup'!B:X}, "SELECT Col1, COUNT(Col22) Group by Col1 label COUNT(Col22)"",0),2,),0),))")))))
Upvotes: 0
Views: 69
Reputation: 13156
You can also use SUMIF
and concatenate the conditions.
=ArrayFormula(IFERROR(1/SUMIF('Employee Leave Setup'!B5:B&'Employee Leave Setup'!X5:X,B2:B&K1:W1,'Employee Leave Setup'!I5:I)^-1))
Upvotes: 1
Reputation: 30309
Try:
=MAKEARRAY(COUNTA(B2:B),COUNTA(K1:W1),LAMBDA(r,c,SUMIFS('Employee Leave Setup'!I:I,'Employee Leave Setup'!B:B,INDEX(B2:B,r),'Employee Leave Setup'!X:X,INDEX(K1:W1,,c))))
Upvotes: 1