Need Help with Array Formula to return multiple results for specified criteria

[worlockb]

Hello Excel Users!

I need some assistance on an array formula for a summary table that will show multiple results for a set of criteria:

• Selected Employee
• Selected Year
• Only Leave Types: Call Off, In Late, Leave Early

The first formula I tried with the leave type conditions but it said I was using too many arguments. Not sure what I am overlooking.
The second I tried without the leave types but everything is returning blank in the results table.

Can anyone assist me?

https://drive.google.com/file/d/1qGJFfnO8fmnOhKlMH1Np5U8ZqkC1f6me/view?usp=sharing

 

[jasonb75]

Looks like you almost had it right,

The formula in A7 should be ROWS(A$7:A7) not ROWS(A$7:A16).

A slightly shorter version that might be easier to manage when adding extra criteria, as long as you're using excel 2010 or newer (doesn't need array confirming). Second one is for all leave types, comparing the ROWS() count B3 will result in missing rows, which is why I removed it from that formula.

=IF(ROWS(A$7:A7)<=$B$3,INDEX(L:L,AGGREGATE(15,6,ROW($L$7:$L$18)/($L$7:$L$18=$B$1)/($R$7:$R$18=$B$2)/ISNUMBER(MATCH($O$7:$O$18,$B$25:$B$27,0)),ROWS(A$7:A7))),"")

=IFERROR(INDEX(L:L,AGGREGATE(15,6,ROW($L$7:$L$18)/($L$7:$L$18=$B$1)/($R$7:$R$18=$B$2),ROWS(A$7:A7))),"")

Also, I noticed that you wrote your countifs formula in B3 longhand, so a slighlty shorter one for that too.

=SUMPRODUCT(COUNTIFS($L$7:$L$18,$B$1,$R$7:$R$18,$B$2,$O$7:$O$18,$B$25:$B$27))