Count range by condition that involves 2 other ranges

Question

Background

• In sheet A: Range A5:A24 [no headers] contains a list of classes (i.e. math, English, etc.)
• In sheet B: Range H5:O [no headers] contains a table of classes events:
  • H5:H is the class name (can be from the list in sheet A or not)
  • I5:I is the teacher's name
  • J5:J is the date
  • K5:O is the attending students' names (can be anything between 1 and 5 names for any class).

Goal

In sheet A, range B5:B24 - I want to have a total count of students for each class in range A5:A24.

Meaning: for each row of range H5:O in sheet B - count string values in columns K to O if cell column H is found in range A5:A24 in sheet A. Then, sum it up per each row in this last range.

This can theoretically be between 0 to infinity (depending on the occurrences of a class and the number of attending students.

• For each row in sheet B - students should be counted (even if it's the same students like in other occurences).

Example Spreadsheet

https://docs.google.com/spreadsheets/d/1T7_zPxbGmyLSSWQ3ur443EY9zjbNhy4EAJyZtXsfr4o/edit?usp=sharing

Answer 1

I think putting this formula in 'Sheet A'!B5:B24 will do the trick:

=counta(iferror(filter('Sheet B'!$K$5:$O,'Sheet B'!$H$5:$H=A5),))