formula - counting

My name is David, librarian from Institute of Oncology Ljubljana.

Would anybody on forum be so kind and help me to solve my problem with bibliographies at our institution.

I am having around 200 authors / researchers (surname, name) in one column in one excel sheet.

In the other sheet I have list of 1.500 works (monographs, articles...) with:
- one column with points for each author for one work
- one column with point per one work
- with one column of authors who wrote them (from 1 up to 30 authors per one work)
- one column with title of the work


points per one author points per one work number of authors name of the authors title of the work
2,5 5 2 Surname1, Name1 / Surname2, Name2 Article no. 1
1,67 5 3 Surname3, Name3 / Surname4, Name4 / Surname5, Name5 Article no. 2
5,00 5 1 Surname1, Name1 Article no. 3
2,50 5 2 Surname3, Name3 / Surname2, Name2 Article no. 4
5,00 5 1 Surname7, Name7 Article no. 5


I would like that excel would count point per every author.

I allready have formula to count the number of authors per one work:
=IF(E2="";0;LEN(E2)-LEN(SUBSTITUTE(E2;"/";""))+1)

Thank you for your help in advance.

Regards,
David

this is a screenshot:
Attachment 524678

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

I am attaching a sample workbook as requested. Thank you.

I made a mistake in attached sample excel file. I am sending the repaired/final one.

Try

in B2

=SUMPRODUCT(--(ISNUMBER(SEARCH($A2,'title list 1'!$D$2:$D$23))))

in C2

=SUMPRODUCT(--(ISNUMBER(SEARCH($A2,'title list 2'!$D$2:$D$23))))*3

in D2

=SUMPRODUCT(--(ISNUMBER(SEARCH($A2,'title list 3'!$D$2:$D$23))))*5

Attached has JeteMc's generic formula (better solution).

It is working! Thank you.