Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

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

Please could you help

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

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

Related Topics:

- CountIF Function
- Excel 2000 Sumif/Countif
- Need some help.
- need help
- A dummy need help
- Need help regarding Multiple sheets
- Need help auto-sorting a data table
- Need help with a Save as maco
- VBA- HELP NEEDED TO LEARN VBA
- Help needed in SQL to Excel VBA Form
- Need Help
- Excel Macro Help Needed
- Graphs Help Needed
- Help needed with Range Copy and Paste
- Compound interest, I need help til the Java Code
- Need Help in JSP
- Need help with macro
- Help needed for a VBA novice with excel charting
- Help Needed on This Excel Problem
- need macro help document
- Need help with code to find data within a cell
- Help needed in VLOOKUP
- Need Help in Excel-VBA
- Need help in finding duplicates
- Excel help needed...