count occurances in a row using multiple criteria

[clarknv]

My spread sheet has my students name, the the date of the class(2 months
total) to the right with 3 items listed under each day (acrf, coachings,
attendance). I would like to count the number of times someone has been
coached. The problem is that the name could be different for each coaching.

How can I count the number of coachings if the criteria can be different and
the criteria is selected from a data validation list?

 

[Bernard Liengme]

I think you need to give a clearer outline of the data layout, please.

 

[clarknv]

(A) (B) (C) (D) (E)
(F) (G)
5/26
5/27
ACRF Coaching Attendance ACRF
Coaching Attendance
Student1 Coach1

Student2
Coach2
Student3 Coach9

Students listed in column A, Date spans 3 columns, under each date are the 3
items that I am tracking (ACRF, coaching, attendance). Also the dates go
from 5/26 to 7/26.

For Student1, I need to count how many times he has been coached, regardless
of who did the coaching. The name of the coach is selected from a data
validation list.

Any help would be greatly appreciated!

 

[Bernard Liengme]

Method 1:
I will assume the student names to be in A1:A200; coaches (or blanks) in
C2:C200
In H2 I entered the name of a student
In I2 I use the formula =SUMPRODUCT(--($A$2:$A$200=H2),--($C$2:$C$200>" "))
to tell me how many time that student had a coach.
If I place another student name in H3 I can copy the formula down a row to
get her count of coaching sessions.
You can adjust the formula to fit your need but DO NOT use full columns such
as A:A
since SUMPRODUCT cannot cope with this.
For more on this topic see:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
http://mcgimpsey.com/excel/formulae/doubleneg.html

Method 2 is to use a Pivot Table. It would take too long to tell how but see
any of these
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/
BE AWARE: pivot tables are not dynamic, if you add/alter the data you need
to recomputed the table - very easily done but must not be forgotten

best wishes