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.

	A	B	C
7	Code	Value	Value_Recode
8	YES-2	2	2 =INDEX($B$8:$B$10,MATCH(A8,$A$8:$A$10,0))
9	PARTIAL-1	1	1 = INDEX($B$8:$B$10,MATCH(A9,$A$8:$A$10,0))
10	NO-0	0	0 = INDEX($B$8:$B$10,MATCH(A10,$A$8:$A$10,0))

<tbody>
</tbody>

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.