# Conditional Count

#### planetpj

##### Active Member
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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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.

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

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!!

Replies
24
Views
907
Replies
2
Views
326
Replies
12
Views
251
Replies
10
Views
778
Replies
1
Views
128

Threads
1,219,688
Messages
6,149,725
Members
450,910
Latest member
DianeG

### 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

### 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