Sum of coding variable by using Index and Match

[greenka]

Dear all,

This is my first time here. Could you please advice me with this question?

Each worksheet called "Project 1, 2, 3, ...., 10" and have 5 questions which each question has drop down to choose 3 variables (YES-2, PARTIAL-1, NO-0). And I want to sum all of 15 questions in another worksheet called "Summary" - which has 10 projects in total.

What I did now is, I recode (YES-2, PARTIAL-1, NO-0) by using function INDEX and MATCH in "Data" worksheet in table below.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Code[/TD]
[TD]Value[/TD]
[TD]Value_Recode[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]YES-2[/TD]
[TD]2[/TD]
[TD]2 =INDEX($B$8:$B$10,MATCH(A8,$A$8:$A$10,0))
[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]PARTIAL-1[/TD]
[TD]1[/TD]
[TD]1 = INDEX($B$8:$B$10,MATCH(A9,$A$8:$A$10,0))[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]NO-0[/TD]
[TD]0[/TD]
[TD]0 = INDEX($B$8:$B$10,MATCH(A10,$A$8:$A$10,0))[/TD]
[/TR]
</tbody>[/TABLE]


As I mentioned, each worksheet called "Project 1, 2, 3, ...., 10" and have 5 questions which each question has drop down to choose 3 variables (YES-2, PARTIAL-1, NO-0). And I want to sum all 15 questions in "Summary" worksheet.

So, in "Summary" worksheet, I created the formula for Project 1 like this =SUM(INDEX(Data!$B$8:$B$10,MATCH('Project 1'!C1,Data!$A$8:$A$10,0))+INDEX(Data!$B$8:$B$10,MATCH('Project 1'!C2,Data!$A$8:$A$10,0))+INDEX(Data!$B$8:$B$10,MATCH('Project 1'!C3,Data!$A$8:$A$10,0))+INDEX(Data!$B$8:$B$10,MATCH('Project 1'!C4,Data!$A$8:$A$10,0))+INDEX(Data!$B$8:$B$10,MATCH('Project 1'!C5,Data!$A$8:$A$10,0)))

If you can see in the formula that, only the cells that I highlighted and underlined. I'm wondering are there any easier function that I can sum these 5 cells (C1-C5)?

I tried all sumif, sumproduct but doesn't work. I don't know how to use VBA. Could you please help? Really appreciated.

Many thanks.