Sum of Questionnaire Scores Based on a Domain Table

H

herman925

New Member
Joined
Apr 9, 2017
Messages
24
I have created a questionnaire that consists of around 100 questions. These items are separated into 6 domains where. For the sake of easier understanding, let's just call them Domain 1 - 6.

I have them typed in one specific table called "Correspondence", with format like below:

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]Question No.
[/TD]
[TD]Domain
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]C
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]C
[/TD]
[/TR]
</tbody>[/TABLE]



I used Google Form to generate a spreadsheet of RAW data of respondents, where it will help me mark the RAW Scores, for each item on a separate column:

(An example)
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD]Submission ID
[/TD]
[TD]Question 1
[/TD]
[TD]Question 2
[/TD]
[TD]Question 3
[/TD]
[TD]Question 4
[/TD]
[TD]Question 5
[/TD]
[TD]Question 6
[/TD]
[/TR]
[TR]
[TD]Participant 1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]Participant 2
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Participant 3
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]



The next thing I need to do is generate another table that sums up the Domain totals for each participant. So from the example above, I need to sum 1,3,5 as Domain A, 4 as Domain B and 2 & 6 as Domain C:

(An example)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Domain A
[/TD]
[TD]Domain B
[/TD]
[TD]Domain C
[/TD]
[/TR]
[TR]
[TD]Total score for Participant 1
[/TD]
[TD]9
[/TD]
[TD]1
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]


The hardest thing is to find a proper method to kick start this process. Can anyone point me in the right direction? Either formulas or VBAs would be fine too. Thanks!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
something like this or I misunderstood ?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Submission ID[/td][td=bgcolor:#70AD47]A[/td][td=bgcolor:#70AD47]B[/td][td=bgcolor:#70AD47]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Participant 1[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Participant 2[/td][td]
9​
[/td][td]
4​
[/td][td]
8​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Participant 3[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
8​
[/td][/tr]
[/table]
 
Upvote 0
ignore my previous post#2

here is with PowerQuery:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Submission ID[/td][td=bgcolor:#70AD47]A[/td][td=bgcolor:#70AD47]B[/td][td=bgcolor:#70AD47]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Participant 1[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][td=bgcolor:#E2EFDA]
7​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Participant 2[/td][td]
15​
[/td][td]
3​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Participant 3[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Table.NestedJoin(Table2,{"Attribute"},Table1,{"Question No."},"Table1",JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(Source, "Table1", {"Domain"}, {"Domain"}),
    #"Grouped Rows" = Table.Group(#"Expanded Table1", {"Submission ID", "Domain"}, {{"Value", each List.Sum([Value]), type number}}),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Domain", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[Domain]), "Domain", "Value", List.Sum)
in
    #"Pivoted Column"[/SIZE]

but before execute M-code above you need to prepare both tables of source

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Question No.[/td][td=bgcolor:#70AD47]Domain[/td][td][/td][td=bgcolor:#70AD47]Submission ID[/td][td=bgcolor:#70AD47]Attribute[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Question 1[/td][td=bgcolor:#E2EFDA]A[/td][td][/td][td=bgcolor:#E2EFDA]Participant 1[/td][td=bgcolor:#E2EFDA]Question 1[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Question 2[/td][td]C[/td][td][/td][td]Participant 1[/td][td]Question 2[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Question 3[/td][td=bgcolor:#E2EFDA]A[/td][td][/td][td=bgcolor:#E2EFDA]Participant 1[/td][td=bgcolor:#E2EFDA]Question 3[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Question 4[/td][td]B[/td][td][/td][td]Participant 1[/td][td]Question 4[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Question 5[/td][td=bgcolor:#E2EFDA]A[/td][td][/td][td=bgcolor:#E2EFDA]Participant 1[/td][td=bgcolor:#E2EFDA]Question 5[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Question 6[/td][td]C[/td][td][/td][td]Participant 1[/td][td]Question 6[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Participant 2[/td][td=bgcolor:#E2EFDA]Question 1[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Participant 2[/td][td]Question 2[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Participant 2[/td][td=bgcolor:#E2EFDA]Question 3[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Participant 2[/td][td]Question 4[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Participant 2[/td][td=bgcolor:#E2EFDA]Question 5[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Participant 2[/td][td]Question 6[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Participant 3[/td][td=bgcolor:#E2EFDA]Question 1[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Participant 3[/td][td]Question 2[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Participant 3[/td][td=bgcolor:#E2EFDA]Question 3[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Participant 3[/td][td]Question 4[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Participant 3[/td][td=bgcolor:#E2EFDA]Question 5[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Participant 3[/td][td]Question 6[/td][td]
2​
[/td][/tr]
[/table]
 
Last edited:
Upvote 0
The RAW data will be constantly modified and not maintained by me and thus I am not sure if I could change the format of the RAW table to the one you specified.....but appreciate the thought!
 
Last edited by a moderator:
Upvote 0
you can change data in source table as long as format and type of data will be the same in appropriate places

changes was maded in PowerQuery - your source table is still the same as in your post
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
895
Fluff
Fluff
R
  • Solved
Scoring table
Replies
9
Views
591
Rock5150
R
E
  • Question Question
Formula that has sum and exception
Replies
4
Views
285
Cubist
Cubist
Y
  • Solved
Need formula to match
2
Replies
10
Views
594
yukonexcel
Y
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
113
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top