Conditional Count

planetpj

Active Member
Joined
Jun 25, 2002
Messages
347
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
ABCDEFG
1SchoolNameNoofstudents TutoredStudentNumbersStudentNameGrade
2BryantBeverlyMakar7
30400005KristinaDuffy2
44000013IraniPrifti2
50198051JanitzaCorcino2
60401136RicardoRuiz2
71797049GermaineNegron3
82396013LuisCruz5
91097060AngelArbelo5
10BlackhamWendyTremblay1
110599146CarolPrado6
12CurialeCarolineFrumento2
139394083LindaMorales3
142697015LouisNieves3
15BlackhamCarlaFontanez2
164000005SaraNauer1
174000056ZorriaVaughn1
Sheet3
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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
ABCDEFG
1SchoolNameNoofstudentsStudentNumbersStudentNameGrade
2Tutored
3BryantBeverlyMakar7
4 400005KristinaDuffy2
5 4000013IraniPrifti2
6 198051JanitzaCorcino2
7 401136RicardoRuiz2
8 1797049GermaineNegron3
9 2396013LuisCruz5
10 1097060AngelArbelo5
11BlackhamWendyTremblay1
12 599146CarolPrado6
13CurialeCarolineFrumento2
14 9394083LindaMorales3
15 2697015LouisNieves3
16BlackhamCarlaFontanez2
17 4000005SaraNauer1
18 4000056ZorriaVaughn1
Sheet1
 

planetpj

Active Member
Joined
Jun 25, 2002
Messages
347
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!!
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top