Issues with Sum, IF, and CountIFS nested fuctions

Hi all. I am having an issue with creating the appropriate functions to answer a summary table I have. Please bear with me as I explain.

I have a data table with various interviews conducted by manager at several different sites. It looks something like this.

table 1.PNG

Each interview has 3 different assessments in which the result for each assessment is either "Pass" or "Fail"

I have another table in which I am trying to extract and summarize the data table. It looks something like this

table 2.PNG

The following is my goal.

In interviews in which there was only 1 fail per interview I would like that counted under the appropriate assessment, per manage, per site. If an interview had two or more fails I would like that to be excluded from the count of the individual assessment and included in the "two or more fails" column


Please let me know if you need further clarification.

Thank you,


Edit : Example doc uploaded

Verbal description is not clear.

This is why we also request expected output(s) to be manually included in the examples. From there we can better back-engineer understanding and from there formula(s).