Hi!

Maureen, I have the following "clumsy" way to offer - it works for me
though.

The bottom line is: it works for me.

However, you're going through a lot of unnecessary steps to arrive at a
conclusion!

=COUNTIF(B4:B400,"y")

That will count ALL instances of "Y". Is this for ALL students as a group or
is it for each individual student?

I think counting the number of meeting dates is a little more tricky, so I
put in an interim step. In Cell C4, (and then copied all the way down to
C400) I put the following: =ISNUMBER(A4) - if there is a date, you'll get
TRUE, and FALSE if the cell is empty. You can then count the number of
TRUE's, by putting the following in C1: =COUNTIF(C4:C400,"TRUE").

This is being redundant! If you enter a date as a true Excel date and not
simply a TEXT entry that looks like a date, and with those true dates in
column A:

=SUMPRODUCT(--(ISNUMBER(A4:A400)))

No need for: =ISNUMBER(A4) or =COUNTIF(C4:C400,"TRUE")

Finding the date of the last attendance is the trickiest, and the way I'd
do
it as follows. Firstly, you need to find the relative position in the list
of
dates when the student last attended, and I'd do that in two steps. In D4
I'd
put the following: =IF(B6="y",ROW(B6)) - this will just give you the row
number (not the cell reference) everywhere there's a "y" and a FALSE for
every "n". Then in cell D1, I'd put =MAX(D4:D400) - this will find the
largest number and therefore the last row where there's a "y".

Finally, you can use this relative position to lookup the date by using
the
following:
=INDEX(A4:A400,D1). You might have to format the cell to display the date
in
the correct format.

You can do all of that in a single operation. Assume you want to know the
last meeting John Smith attended.

Column A are the dates. Column B are the student names and column C is the
attendance value of Y (Yes) or N (No):

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(B1:B10="John Smith",IF(C1:C10="Y",A1:A10,"")))

Format the cell as DATE.

Just some thoughts!

Biff

"Huw Davies" wrote in message
...
Maureen, I have the following "clumsy" way to offer - it works for me
though.

Starting with a blank sheet, leave a couple of blank rows at the top for
some formulas, colum titles in row 3, data from row 4 down to say row 400.

Col A is for the dates, and I'm assuming two things here, 1) that the
dates
are only put in when the meeting takes place, and 2) that they're in order
(you'll see why later). Col B is for the "y" or "n" to indicate if the
student attended,

The easiest one to do is the number of attendances and you can do that
with
the following in cell B1: =COUNTIF(B4:B400,"y").

I think counting the number of meeting dates is a little more tricky, so I
put in an interim step. In Cell C4, (and then copied all the way down to
C400) I put the following: =ISNUMBER(A4) - if there is a date, you'll get
TRUE, and FALSE if the cell is empty. You can then count the number of
TRUE's, by putting the following in C1: =COUNTIF(C4:C400,"TRUE"). Having
now
got numbers for the number of meetings and the number of attendances, you
can
do your percentage.

Finding the date of the last attendance is the trickiest, and the way I'd
do
it as follows. Firstly, you need to find the relative position in the list
of
dates when the student last attended, and I'd do that in two steps. In D4
I'd
put the following: =IF(B6="y",ROW(B6)) - this will just give you the row
number (not the cell reference) everywhere there's a "y" and a FALSE for
every "n". Then in cell D1, I'd put =MAX(D4:D400) - this will find the
largest number and therefore the last row where there's a "y".

Finally, you can use this relative position to lookup the date by using
the
following:
=INDEX(A4:A400,D1). You might have to format the cell to display the date
in
the correct format.

Hope this helps,

Huw.


"tannersnonni" wrote:

Gosh thanks for helping...let me see if I can explain.
I need a row with a box for each Sunday of the month. Then the student
will
check the box if they are in attendance. I need a way to see what
percent
of Sundays up to any given point in time they have attended. So if 10
Sundays of the year have passed and they were there 8 of them it would
show
80%. But then the next Sunday rolls by and they don't attend. So the
%Attendence box automatically updates to only 8 out of 11 Sundays
present.
Does that make sense? So it is a constant "real time" percentage. Is
that
possible?
THEN... I need a box that shows the actual last date they were in
attendence.
Any help will be greatly appreciated.
maureen

"Biff" wrote:

Hi!

Sounds fairly easy!

What exactly are you having trouble with? Need very specific details,
with
emphasis on details like ranges , formulas, etc.

Biff

"tannersnonni" wrote in
message
...
Hi there...I am new here. I am looking for some help. I thought I
knew
how
to use excel with many nested ifs and such but then I tried doing
this
one!!
When dates are involved it becomes so confusing. I use excel office
xp
professional.
I am trying to set up a sign in sheet for attendance of meetings.
Then I
want a column that sums the number of times attended and the
percentage of
the overall attendance at any given time. So as the months go along
and
time
passes I want the total of days to increase and the percentage to
change
accordingly. So at anytime I could go in and see what percentage a
student
attended class over the number of classes given over the year. Next
to
that
column I would like a column that shows the last date attended.
Any help will be GREATLY appreciated. I think dates are so
confusing!
thanks so much...maureen