Conditional Count

[planetpj]

What I am trying to do is get a conditional count to count how many students have been tutored by each certain tutor. I can do this manually by each tutor but I am looking for a formala that will recognize when the new tutor starts and then start to count.

jim betz tutoring.xls
	A	B	C	D	E	F	G
1	School	Name		Noofstudents Tutored	StudentNumbers	StudentName	Grade
2	Bryant	Beverly	Makar	7
3					0400005	KristinaDuffy	2
4					4000013	IraniPrifti	2
5					0198051	JanitzaCorcino	2
6					0401136	RicardoRuiz	2
7					1797049	GermaineNegron	3
8					2396013	LuisCruz	5
9					1097060	AngelArbelo	5
10	Blackham	Wendy	Tremblay	1
11					0599146	CarolPrado	6
12	Curiale	Caroline	Frumento	2
13					9394083	LindaMorales	3
14					2697015	LouisNieves	3
15	Blackham	Carla	Fontanez	2
16					4000005	SaraNauer	1
17					4000056	ZorriaVaughn	1
Sheet3

 

[Barry Katcher]

Use the Data/Sub-total function. First, copy the teachers name to every row with their students. Then, get rid of the current rows with the manual counts you are showing.

Click on "Data/Sub totals" and, in the drop down box, select:

At each change in name: Name
Use function: Count
Add Sub total to: Student Name

This will insert a new row below the last occurrence of each teacher's name and show a count of the students under the last student's name.

 

[Aladin Akyurek]

Given your sample data...

In D3 array-enter & copy down:

=IF(LEN(E3),"",MIN(IF(LEN(E4:$E$19)=0,ROW(E4:$E$19)))-CELL("row",E3)-1)

See...

planetpj.xls
	A	B	C	D	E	F	G
1	School	Name		Noofstudents	StudentNumbers	StudentName	Grade
2				Tutored
3	Bryant	Beverly	Makar	7
4					400005	KristinaDuffy	2
5					4000013	IraniPrifti	2
6					198051	JanitzaCorcino	2
7					401136	RicardoRuiz	2
8					1797049	GermaineNegron	3
9					2396013	LuisCruz	5
10					1097060	AngelArbelo	5
11	Blackham	Wendy	Tremblay	1
12					599146	CarolPrado	6
13	Curiale	Caroline	Frumento	2
14					9394083	LindaMorales	3
15					2697015	LouisNieves	3
16	Blackham	Carla	Fontanez	2
17					4000005	SaraNauer	1
18					4000056	ZorriaVaughn	1
Sheet1

 

[planetpj]

Thank You very much. I have been working at it on and off for days Aladin you saved the day again!! That's exactly what I am looking for!!What a genious!!