Dear all,
This is my first time here. Could you please advice me with this question? I already try to search but couldn't find the answer.
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.
<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 me? Really appreciated.
Many thanks.
Green
This is my first time here. Could you please advice me with this question? I already try to search but couldn't find the answer.
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 me? Really appreciated.
Many thanks.
Green