Comparing data based on name which is not in same row

[lifeguard23]

I'm needing to compile data based on student performance over 3 terms of the school year.

The example file is stored at:
http://dl.dropbox.com/u/5892902/example.xlsx

Each term, my school creates a list all the students who obtain either Honour Roll (A or B) and Effort (E) Standing. It uses a 5 Column System (Lastname, FIrstname, Grade, Honour Roll, and Effort). I've shaded Term 1 Green. Then the same thing is done for Term 2 (red) and Term 3 (blue). I need to create a list of how many students were on an honour roll (A, B, or E) for ALL THREE terms.

The problem is that it's not the entire student population listed in each term. It's only the students that got that standing for that term. So you can't use a simple COUNT formula based on row. I need a way to check the compile the total amount of honour roll standings for each student.

For example "Jon Aalhus" only made B honour roll for Terms 2 and 3, not 1. So his result should be "2" for total term honour roll rankings.

 

[GDH]

Hi

Try the following

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Last Name	First Name	In all 3 terms	Grade	Honour Roll	Effort	Last Name	First Name	Grade	Honour Roll	Effort	LAST	FIRST	GRADE	HR	EFFORT
2	Abbott	Matthew	FALSE	8	B	E	Aalhus	David	9	B		Aalhus	Jonathan	11	B
3	Abrahamson	Chase	FALSE	8	B	E	Aalhus	Jonathan	11	B		Adams	Chloe	8	A	E
4	Adams	Chloe	TRUE	8	A	E	Abrahamson	Chase	8	B	E	Adlani	Amine	10	B
5	Adlani	Amine	FALSE	10	B		Adams	Chloe	8	A	E	Al Taher	May	10	A	E
6	Adrian	Samuel	FALSE	10	B		Aichele-Jones	Madison	9	B		Alain	Jeremy	8	B
7	Adrian	Sarah	FALSE	9	B		Al Taher	May	10	A	E	Alain	Jessica	12	A	E
8	Al Taher	May	TRUE	10	A	E	Alain	Jeremy	8	B	E	Alatrash	Tamir	8	B
9	Alain	Jeremy	TRUE	8	B	E	Alain	Jessica	12	A	E	Alatrash	Maureen	10	B	E
10	Alain	Jessica	TRUE	12	A	E	Alatrash	Maureen	10	A	E	Alatrash	Natalie	11	A	E
Sheet

 

[lifeguard23]

This looks good, however what about for the kids that are ONLY in Term 2 or Term 3?
(eg. Maurene Alatrash)

Ideally, I'd like to have each student be given a total # of Honour Roll standings (1-3) and a total # of Effort standings (1-3)

 

[GDH]

Firstly I would suggest creating a list of all students. If your data was arranged running downwards this you would be able to create 1 list of all students without duplicates (using either advanced filter or the data, remove duplicates on the ribbon). Then you could do a countif based on the name of each student.