CODEWITHSUNDEEP
google-sheets
HSHO
HSHO

Reputation: 537

How to convert SUMIFS to Array Formula Google Sheets

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),))")))))

Sheet

Upvotes: 0

Views: 69

Answers (2)

z..
z..

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

rockinfreakshow
rockinfreakshow

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

enter image description here

Upvotes: 1

Related Questions