Search:

# Need help for COUNTIF

Asked By: Lucina    Date: Oct 25    Category: MS Office    Views: 894

I have a spreadsheet containing the attendance of students. I would
like to have the count number of students present on specific given
date.

Name 01/02/2008 02/02/2008 03/02/2008
aaa X X X
bbb X X X
ccc X X A
ddd A X X
eee X A X

In sheet 2 in one cell(A1) If I enter date,the count of x should
appear in cell(B1)for that particular date

Share:

(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:

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,
For Each Data In DateRange.Rows(1).Cells
If (ChkDate = Data.Value) Then
Check_Attendance =
Application.WorksheetFunction.CountA(Sheets("Sheet1").Range(Cells(2,
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...

Didn't find what you were looking for? Find more on Need help for COUNTIF Or get search suggestion and latest updates.