Sum of column based on criteria of multiple columns and rows

[Robs237]

[FONT=&quot]Hi All, [/FONT]
[FONT=&quot]I have an extremely complex (well it is to me) question that I am hoping someone can assist me with. I'll go through the scenario. [/FONT]
[FONT=&quot]I am trying to gather data by surveying some people. Each question links to a particular category (where I've allocated it either a +1 or a -1 score to determine characteristics depending on whether the person agrees or disagrees with the statement). [/FONT]
[FONT=&quot]e.g referring to the table below if they select "agree" to Question 1 they will get a +1 score for CAT7 (Category 7). If they Agree with Question 5, they get a -1 for CAT4 and a +1 for CAT8. [/FONT]
[FONT=&quot]I have tried SUMPRODUCTS, INDEX, MATCH, VLOOKUPS, COUNTIFS - I could be missing something.[/FONT]
[FONT=&quot]I basically want scores for each category [/FONT]
[FONT=&quot]Please help! It's much appreciated! [/FONT]
[FONT=&quot]

[/FONT]

 

[Special-K99]

"referring to the table below"

There's no table below.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Robs237]

"referring to the table below"

There's no table below.

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

That's odd, I can see an image on my screen. My apologies.

Essentially I have a results table that looks like this

.............Q1/ Q2/ Q3/ Q4
Person 1 A... A/ A/ D
Person 2 A.. A/ A/ A
Person 3 D.. D/ A/ A

(where A and D are "Agree" or "Disagree" - the "/" is just to separate)

Then, I have another sheet that looks like this:


..........CAT1 /CAT2 /CAT3 /CAT4
Q1................-1
Q2 .................1/.......-1
Q3........1/.........................-1

the second sheet is giving a score for each question, against a category, if the person agrees with the question/statement.

I want to sum their scores each time they agree with the statement in a separate sheet.

Any help?

Thanks

 

[Special-K99]

In your second table Q2 and Q3 have two different values +1 -1 depending on the category.
But your first table doesn't have any category details for those questions so you cant identify a specific category.
So are you looking for your output to be something like this?

          Cat 1 Cat 2 Cat 3 Cat 4 etc
Person 1
Person 2
Person 3

BTW I still cant see any image in your first posting.
But if the link is referring to an image on your PC then we wont be able to see it.

 

[Robs237]

Apologies that the image is not working.
That is the output table I am after.
Yes, each statement feeds into a category as either 0 (no effect), +1 (agree with question), or -1 (disagree with question).

Do you know any way I will be able to do that?
Thanks