excel sum values if cell matches a value

[Dennis]

I am trying to analyze a survey. Results are like:

A B C D E F G H I J K
Q1 ? 1 1 1 1
Q2 ?
Q3 ? 1
Q4 ?
Q5 ? 1 1 1 1
Q6 ? 1 1 1 1
Q7 ? 1 1 1 1 1

I have about 30 questions and 200 responses (going up to column FR). The
one represents they checked that box.

In column B I want to add up all the responses of that row is they selected
Q6. So in my example Question 1 B should equal 1 because I only would select
the G response.

And then for question 5, I want to sum up the people who selected Question
6, which would give me a total of of 2. Max sense?

Any ideas on how to do this? SUM(IF isn't working.

 

[Jacob Skaria]

Try the below formula in cell B1 and copy down as required...

=SUMPRODUCT((C1:FR1=1)*($C$6:$FR$6=1))