count occurances in a row using multiple criteria
 

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?

count occurances in a row using multiple criteria
 

I think you need to give a clearer outline of the data layout, please.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"clarknv" wrote in message
...
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?

count occurances in a row using multiple criteria
 

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

I think you need to give a clearer outline of the data layout, please.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"clarknv" wrote in message
...
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?

count occurances in a row using multiple criteria
 

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/2...le-parameters/
BE AWA 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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"clarknv" wrote in message
...
(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" wrote:

I think you need to give a clearer outline of the data layout, please.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"clarknv" wrote in message
...
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?