Forum Discussion
How to Add Multiple Data from Different Location with Intervals in Rows and Columns
I have 300 survey data from individual respondent, each data has a scale of 1 - 4 and have two to three subtopic every subject. How can I total or add every data based on their answer?
Example:
Respondent 1 choose scale of 4 in Subject One, scale of 3 in Subject Two
Respondent 2 choose scale of 2 in Subject One, scale of 1 in Subject Two
Tallying Table:
Subject One has a total of 1 in Scale 4 and total of 1 in Scale 2
Subject Two has a total of 1 in Scale 3 and total of 1 in Scale 1
Formula Used:
for Subject One - SUM(C9,C16,C23,C30,C37)
for Subject Two - SUM(D9,D16,D23,D30,D37)
These formulas are working, problem is what if my Rows are 300, means I need to write or select it manually each cell - SUM(D9,D16,D23,D30,D37, so on ...) there's an interval of 7 on each cell rows.
Is there a way to shorten this formula specially when I copy it on next row or column? I attached the sample excel file.
I will appreciate any help, Thank you very much in advance.
13 Replies
I've done lots of rating excel sheets in the past and the was I do it is much simpler that the setup you designed but it it more precise to do calculation. Hopefully, this revised worksheet would work for you so it will be more easier to load data since each column represents one respondent. I filled in the calculations for the first few cells in the Tally sheet and I'm pretty sure you could fill in the rest with the same formula pattern and just different rows.
- Thanks a lot, I'll surely try this one too.
With SEQUENCE, if you prefer to use SUM and find dynamic arrays exciting.
=SUM(OFFSET(C2,SEQUENCE(MAX($A:$A),1,7,7),0))
- Thanks for the help, I will try it too.
- Your life would be a lot easier if your tallying table were the same way around as your data.
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.
