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

worlockb

New Member
Joined
Nov 9, 2019
Messages
4
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
7,968
Office Version
2019
Platform
Windows
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))
 

Forum statistics

Threads
1,089,234
Messages
5,407,049
Members
403,119
Latest member
Lara465

This Week's Hot Topics

Top