You can do this with a single CountIf function. Say you put your
header info in row 5 and the rest of your data in rows 6-10. You
put a "test" date  in A1. What you want in B1 is:

=COUNTIF(OFFSET(A6:A10,0,MATCH(A1,B5:D5,0)),"X")

You said you wanted the "test" date and attendance count  to be on
Sheet2. In that case, you'd need to add "Sheet1!" in front of each
cell reference from sheet  1. Like this:


=COUNTIF(OFFSET(Sheet1!A6:A10,0,MATCH(A1, Sheet1!B5:D5,0)),"X")

That expression will count the attendance (from sheet 1) for the
date you specify in A1 on Sheet 2.