Tabulate value based on information on 2 spreadsheets

Hello everybody,

New to this forum and not an Excel expert... I am here seeking your help with a formula that has been driving me nuts for a few days:

I have a spreadsheet that is produced as a result of a questionnaire being filled out. There are 15 questions in total and the end result is the "Test" table that looks like this:

Participant Test Step 1 DE Test Step 2 DE Test Step 3 OE Test Step 4 AD Test Step 5 DE
J.SM Y N N/A N N/A
M.K Y N Y Y Y

In a different spreadsheet (same file) I have assigned a score to each of the questions. The "Scores" table looks like this:

Test Step 1 DE Test Step 2 DE Test Step 3 OE Test Step 4 AD Test Step 5 DE
5 3 8 2 12


I am trying to come up with a formula that will identify among the headers of the "Test" table the category "DE" and add the scores from the "Scores" table mapped to those specific questions but ONLY if the answer is "Y". Then, the formula needs to add the scores in the "Scores" table for the category "DE" but only if the answer was "Y" or "N" (must disregard the "N/A"). The end result is the product of dividing the result of those 2 numbers. I know.. sounds complicated...

To illustrate this with an example based on the tables above:

For J.SM the score of "Y" answers to the "DE" questions is 5 (only question 1 was answered with a "Y"). The base score against which this number will be tabulated is 8 (Question 1 + Question 2. The N/A given to Question 5 has been disregarded). Therefore the result of the formula would be 5/8=62.5%. For M.K the result would be (5+12)/20 = 85%

So far I have been able to produce the first value with this formula:

=SUMIFS(Scores!A3:O3,Test[[#Headers],[Test Step 1 AD]:[Test Step 15 OE]],"*DE*",Test[@[Test Step 1 AD]:[Test Step 15 OE]],"Y")

But I have been unable to produce the base value for the base score (sum of all "Y" and "N" "DE"s.

Any help will be deeply appreciated.

Thank you so much!

Marta