Find occurrence of a text in a column and calculate using the same

[S. Das]

Hi,
I am facing a problem in a worksheet. I have 5 subject and the total number of the candidate in those subjects.

The user will give the subject name and the number of the student in a classroom, and the system has to show the number of the remaining student for that subject.

Here I am attaching a sample file for better understand.

Note : 1) No fixed position for a subject, i.e, any subject can be at any position
2) Any new subject can start from any room say room no. 6 or 11.
3) For a particular classroom number of the subject can be in between 1 to 5, i.e, maximum 5 subjects in a classroom.

 

[bosco_yip]

Maybe,

In D5, copied down :

=IF(B5="","",HLOOKUP(B5,A$2:E$3,2,0)-SUMIF(B$5:B5,B5,C$5:C5))

Regards
Bosco