Array that pulls list of data based on sum of multiple criteria

[moses125]

Hello!

I have a spreadsheet that tracks attendance and I need to create a list of outstanding instances. I already have an array that pulls the list of advisors that have a specific text (example 1), and now i need to pull a list of names that have less than 2 occurances.

'Attendance' Table Example
Name - Type - Date
John Smith - 1Tardy - 1/1/15
John Smith - 2Tardy - 1/2/15
John Smith - Absence - 1/3/15
Scott Jones - 1Tardy - 1/4/15
John Smith - 3Tardy - 1/6/15
John Smith - 4Tardy - 2/5/15

EXAMPLE 1:
The first array i have is: (O2 = "3Tardy")
{=IFERROR(INDEX(Attendance!A$2:A$3000,SMALL(IF(Attendance!$B$2:$B$3000=$O$2,ROW(Attendance!A$2:A$3000)-ROW(Attendance!A$2)+1),ROWS(Attendance!A$2:Attendance!A2))),"")}

So based on the above table it would only display John Smith.

Now, what I'm trying to do is list the names of those that only have less than or equal to 2 instances in ColumnB with "*Tardy". Then, to make it more complicated, only display the name if the date range is in January. (start and end dates for each month are listed in another cell to refrence - N1 & O1). So, based on the above example it should only display Scott Jones in January and both John Smith and Scott Jones for February.

Ive tried this:
{=IFERROR(INDEX(Attendance!A$2:A$3000,SMALL(IF(COUNTIFS(Attendance!B2:B3000,"*Tardy",Attendance!C2:C3000,">="&N1,Attendance!C2:C3000,"<="&O1)<=2,ROW(Attendance!A$2:A$3000)-ROW(Attendance!A$2)+1),ROWS(Attendance!A$2:Attendance!A2))),"")}
but it returns any name listed, even if they have more than 2 Tardies listed in the Attendance sheet.

Any suggestions on how I can make this work like the first formula? what am I missing? and thank you in advance for any assistance!

[Tony Valko]

Can you post a SMALL sample file and show us what result you expect?

A SMALL file will have about 20 rows worth of data.

[moses125]

Ok, So I created a smaller version included below. The PA tab has been set up to show (A6:B15) any advisor that has 3 Tardies and is due for a write up, or 1 Absence (D6:E15). If they have been delivered it already it shows the date, if not the Team Leaders name that should deliver it.

What I’m looking for is the data on the COY tab. The criteria I need is if the person has 2 tardies or less and no absences and between the dates listed in N2:O2. I just can’t figure out how to make it count any *Tardy listed on the attendance tab by each name.

According to the data on the Attendance tab, selecting January on the COY tab should only produce Scott, Jones. (has 2 Tardies); and for February Morris, Zak.

As I stated above i tried: {=IFERROR(INDEX(Attendance!A$2:A$3000,SMALL(IF(COUNTIFS(Attendance!B2:B3000,"*Tardy",Attendance!C2:C3000,">="&N1,Attendance!C2:C3000,"<="&O1)<=2,ROW(Attendance!A$2:A$3000)-ROW(Attendance!A$2)+1),ROWS(Attendance!A$2:Attendance!A2))),"")}
but it will return every single name with a *Tardy in the attendance tab, and for each occurrence listed.

[moses125]

Hello,

So i was able to find a workaround (putting the sum totals in another location and using the formula to look there) but now i have another issue that i cant get to work.

i have the array formula:
{=IFERROR(INDEX(Attendance!I$2:I$3000,SMALL(IF(Attendance!$A$2:$A$3000=$E$2,ROW(Attendance!A$2:A$3000)-ROW(Attendance!$A$2)+1),ROWS(Attendance!A$2:Attendance!A2))),"")}
Which pulls all records matching info in E2, but i need to add a second if: if(attendance!$C$2:$C$3000">="&Data!$D$14

i tried nesting: {=IFERROR(INDEX(Attendance!I$2:I$3000,SMALL(IF(Attendance!$A$2:$A$3000=$E$2,if(attendance!$C$2:$C$3000">="&Data!$D$14
,ROW(Attendance!A$2:A$3000)-ROW(Attendance!$A$2)+1),ROWS(Attendance!A$2:Attendance!A2))),"")}
Which would pull all records matching info in E2 and if date is greater than Data!D14 (1/1/15),but it still says theres an error in the code. any suggestions/tips on how to nest the second if?