(Your test data isn't very good - each day has attendance of 4. You need
different attendances on different days to get any results out of this.)

CountIf doesn't do things of this complexity, nor does any other standard
function. The idea of Excel is to stage your calculations. I.e. do
intermediate calculations, then roll them up into final calculations.

I've put your test data (with a couple of X's changed to A's) into the
following places:

Heading row: A14:D14
Blank row: A15:D15
Rest of it: A16:D20

In the blank row, put CountIf totals for each day. E.g. B15 is

=COUNTIF(B16:B20,"X")

which is copy/pasted to C15 and D15.

So now you have totals for each day. Next step is to select the one you
want. For this, you use HLookUp.

=HLOOKUP(A1,B14:D15,2,FALSE)

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.

I think this should work.


if(sheet1!a1=date,countif("x",range))

Is there any problem with putting the totals in the column with the list?
If you could use a function like: =COUNTA(B2:B50), =COUNTA(C2:C50), etc
in each column, (and put it, say, row 51) then you can use hlookup to get the
number:
=HLOOKUP(A1,Attendance!B1:U51,51,FALSE)

If you'd prefer a VBA solution:

In a VBA Module, I put:
Global DateCnt, DateCol, DateRange, Data
Public Function Check_Attendance(ChkDate)
If (IsDate(ChkDate)) Then
DateCnt =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range("A1:IV1"))
Set DateRange = Worksheets("Sheet1").Range("A1:" & Cells(1,
DateCnt).Address)
For Each Data In DateRange.Rows(1).Cells
If (ChkDate = Data.Value) Then
Check_Attendance =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range(Cells(2,
Data.Column).Address & ":" & Cells(1000, Data.Column).Address))
Exit For
End If
Next Data
End If
End Function

then, in Sheet2, cell B2, I used the function:
=Check_Attendance(A1)

I'm pretty sure there's a cleaner way to define the range for the CountA
worksheetfunction,
but I kept getting errors. this works, so I let it run...