Sum of column based on criteria of multiple columns and rows

Robs237

New Member
Joined
Oct 11, 2017
Messages
3
[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]
1133957
[/FONT]
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
"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
 
Upvote 0
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?


Code:
          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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,164
Members
453,021
Latest member
Justyna P

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