Complex INDEX/MATCH or SUMIFS formula

[Marta GH]

Hello,

I have been struggling for a while with this one and decided to seek your help in solving my issue.

I have a 20 step questionnaire data that comes in the following format and it is downloaded into an excel spreadsheet in a table that looks like this (the table is called "test"):

[TABLE="width: 326"]
<colgroup><col style="text-align: center;"><col span="4" style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Step 1[/TD]
[TD="align: center"]Step 2[/TD]
[TD="align: center"]Step 3[/TD]
[TD="align: center"]Step 4[/TD]
[TD="align: center"]Step 5 [/TD]
[/TR]
[TR]
[TD="align: center"]N[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]N/A[/TD]
[/TR]
</tbody>[/TABLE]

The only possible answers are "Y", "N" or "N/A" for each of the steps and there could be more than a hundred questionnaires completed (rows).

In a separate spreadsheet in the same file, I have a scorecard table that shows the values assigned to each of the 20 steps. It looks like this (the table is called "Scores"):

[TABLE="width: 389"]
<colgroup><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"><col style="text-align: center;"></colgroup><tbody>[TR]
[TD="align: center"]Step 1[/TD]
[TD="align: center"]Step 2[/TD]
[TD="align: center"]Step 3[/TD]
[TD="align: center"]Step 4[/TD]
[TD="align: center"]Step 5[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]12[/TD]
[/TR]
</tbody>[/TABLE]

I am trying to build a formula that will identify the heading of each question (Step 1-20), look up each heading among the columns on the scorecard table, locate the number assigned to it if the answer is either “Y” or “N” (ignore the “N/A”s) and add all those numbers for the base score.

Then, (can be split into to or more formulas), the formula will add only the “Y” answers and tabulate them against the base score for a final score.

For illustration purposes the formula would, for the tables above, produce the following: Add 2+1+3+5=11, then add 1+3, then divide 4/11 for a final score of 0.36 (or 36%)

I started by using an index/match combo but can't find a way to incorporate the condition for "Y" and "N": =INDEX(Scores!$A$3:$O$3,MATCH(Test!A2,Scores[#Headers],0))+INDEX(Scores!$A$3:$O$3,MATCH(Test!B2,Scores[#Headers],0))+INDEX(Scores!$A$3:$O$3,MATCH(Test!C2,Scores[#Headers],0))+INDEX(Scores!$A$3:$O$3,MATCH(Test!D2,Scores[#Headers],0))+INDEX(Scores!$A$3:$O$3,MATCH(Test!E2,Scores[#Headers],0))

About to toss the index/match because I think it is too complicated.. looking for a simpler solution. Really stuck with this one… any help is truly appreciated.

Thank you so much in advance for any help or guidance you can offer,

Marta

 

[Roxxien]

Welcome on the forum!

Does you absolutely need to have this as an Excel formula or a VBA based formula could be correct?

A solution containing all what you need/want for 20 step could be pretty huge with an Excel formula.

 

[Marta GH]

Thank you Roxxien for your response and warm welcome!!

I could totally go via VBA... I agree that the formula could be huge but then again, I would still need to build the formula into the macro regardless.

Any guidance would be very much appreciated!!

Marta

 

[Roxxien]

I created an User Defined Function (UDF) for your problem.

The function have two parameter.
1. You put the range of your X step.
2. You put the range of your score. (need to put the same number of cell)

Function CorrectTest(ByVal Answer As Range, ByVal Score As Variant)
Dim cgood As Currency
Dim ctry As Currency
Dim i As Integer
Dim r As Range


'Loop through each answer
For Each r In Answer
    i = i + 1
    'If diffrent then N/A then add to "try" score
    If Answer(i) <> "N/A" Then
        ctry = ctry + Score(i)
        'If good answer then add to "good" score
        If Answer(i) = "Y" Then
            cgood = cgood + Score(i)
        End If
    End If
Next


'Calculte the final score
CorrectTest = cgood / ctry
End Function

Now, how to use an UDF, highly suggested to firstly try in a copy of the original workbook.
1. Open the VB editor in Excel with Alt+F11
2. Create a new module by right click on the left window then insert->Module
3. Paste the code above COMPLETELY
4. Close the VB editor
5. "Save as" your workbook in a format the enabled macro (.xlsm for example)
6. You now have a new Excel function in that workbook that you can use like any other natural function

 

[Marta GH]

Holy cow... you are an absolute genius!!! Thank you SO MUCH!!!! Way beyond my wildest expectations.

THANK YOU!!!!!

Marta

 

[Roxxien]

It have been my pleasure to help you with that.

It wasn't more complicated to have a variable number of cell then only for 20 cells.

Roxxien

If you have other problem, don't hesitate to come back on this forum.

 

[Marcelo Branco]

Or maybe this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Step 1​

[/td][td]

Step 2​

[/td][td]

Step 3​

[/td][td]

Step 4​

[/td][td]

Step 5​

[/td][td]

Result​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

N​

[/td][td]

Y​

[/td][td]

Y​

[/td][td]

N​

[/td][td]

N/A​

[/td][td]

36%​

[/td][/tr]


[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Y​

[/td][td]

Y​

[/td][td]

N​

[/td][td]

N/A​

[/td][td]

Y​

[/td][td]

83%​

[/td][/tr]
[/table]

Formula in F2 copied down
=SUMIF(A2:E2,"Y",Scores[[Step 1]:[Step 5]])/SUMIF(A2:E2,"<>N/A",Scores[[Step 1]:[Step 5]])

M.

 

[Marta GH]

That also works beautifully... thank you so much!!!!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.