Forum Discussion
How to Add Multiple Data from Different Location with Intervals in Rows and Columns
A formula that can copy rightwards and is the general idea of where to head: =SUMIFS($C$9:$C$40,$B$9:$B$40,C$1)
- Hi 🙂 Thanks for the quick reply, maybe I can't explain properly what I want to happen, if you have time please check the attached file so you can have more idea on what I'm trying to do. Thanks 🙂
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.
- Thank you very much, I will consider this one too.
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
- Thanks for the help, I will surely try it.
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.
