How to Add Multiple Data from Different Location with Intervals in Rows and Columns

erick_delmundo 

Let me suggest a different approach altogether. This involves a change in the way you record the responses, as illustrated on the attached. And then let Excel's Pivot Table capability do the tallying for you.

Please note: I did not try to replicate your responses, just randomly entered responses on the 1-4 scale into this different way of recording. That is to say, I am just illustrating the process.

You can test if by changing the results for the first three respondents to be the same as yours. Just know that it will work with your data and you won't need to write any formulas at all. Just make use of the Pivot Table.

erick_delmundo 

As variant in C2

=SUMPRODUCT(($B$8:$B$40=C$1)*IF(ISNUMBER(INDEX($C$8:$L$40,0,MATCH($B2,$C$8:$L$8,0))),INDEX($C$8:$L$40,0,MATCH($B2,$C$8:$L$8,0)),0))

and drag it to the right (within the block) and down

erick_delmundo 

This is the idea. Place this in C2:

=SUMIF($B$9:$B$1000,C$1,$C$9:$C$1000)

Saviaalso has the right idea.  Conditional sum will make things much easier whichever route you go.