Count blanks in Data Validation

[Guest]

I have a spreadsheet that tracks attendance violations by the week. Each day
has a data validation list to select Cancel (CA), No Show (NS), Late (L),
Leave Early (LE) and Violation (V) status.
I would like a formula or code that will check to see if each of the days in
the week were left blank (no attendance violations) and return all the names
of the students with perfect attendance.
Data Validation list is named range: "Attend"

Any help would be greatly appreciated.

 

[Guest]

One way ..

Assume names are listed in A2 down, with corresponding attendance records
for day1-day5 (for 1 week) in cols B to F.

Using 2 empty cols to the right, say cols H & I

Put in H2:
=IF(COUNTBLANK(B2:F2)=5,ROW(),"")
Leave H1 blank

Put in I2:
=IF(ROW(A1)>COUNT(H:H),"",INDEX(A:A,SMALL(H:H,ROW(A1))))
Select H2:I2, copy down to the last row of data in col A. Col I will return
required the list of names with perfect attendances, all neatly bunched at
the top. Adapt to suit.

 

[Dave Peterson]

I'd add another column for each week. Then put this kind of formula in it:

=COUNTIF(A2:E2,"<>")
(and drag down)
Where A:E is a 5 day week.

Then apply data|filter|autofilter to that column.

Filter to show the 0's and you'll have the kids with perfect attendance.

You could also count individual violations:
=countif(a2:e2,"L")
would count the Late's.