Tabulate value based on information on 2 spreadsheets

[Marta GH]

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

[Marta GH]

Still unable to find the answer to this question. Any help will be very much appreciated.

Thank you!

[JeteMc]

Hello Marta GH and Welcome to Excel Forum.
Without a sample of the spreadsheets it is difficult to say with any certainty, however since you say the formula in post #1 works and assuming that the only possible values are 'Y', 'N' and 'N/A' (meaning that there are no blanks), then you may want to try the following:

Please Login or Register  to view this content.

If this formula fails to give the expected results then I would suggest uploading a small sample of the spreadsheets with expected results manually entered.
To upload sample spreadsheets click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.