0

I am trying to filter rows by a name and then COUNTIF a column that represents a question that was answered in a "Strongly Agree / Agree ..etc" manner to calculate how many of each category for each question was selected.

Example Data:

name | question 1| question 2 ... ============================= Bob | Agree | Disagree Bob | Strongly A| Agree Red | Disagree | Strongly Agree Blue | Agree | Agree

I'd like to get this data from the above:

Bob | question 1 | question 2 ... ================================== Agree | 1 | 1 Disagree| 1 | 1 ...

(For those curious: it is an anonymous survey about specific people.)

I do not have a lot of experience with excel I came up with this so far: =COUNTIF(IF(Sheet1!A2:A5="Bob",Sheet1!B2:B5, ""), Sheet2!A2)

This gives me a #VALUE!. I am not exactly sure how to get the range to be just the rows of the related question with the matching name value. Any help would be greatly appreciated!

Improve this question

1 Answer 1

Reset to default
2

You're trying to sum with 2 criteria (the name column and the question column have to be equal to certain values). For that reason, I don't think countif() would work for you.

2 options come to mind:

  1. Pivot tables. Goto Insert->Pivot Table and create one in a new tab. Drag&drop your name column into the Report Filter, q1 into the Row Label, and count of name as your Value. Thus, without requiring any code, you can sum one question at a time. You can also get exactly your target table, but first you'd have to turn your table into 3 columns: Name, Question#, and Answer.

  2. Sumproduct() function. Here's one example, but you can google for more. With this function, you can count occurrences of multiple criteria at once. Using your example, this would return the number of rows in your table where the name is Bob and Question1 is Agree:
    =SUMPRODUCT((A2:A5="BOB")*(B2:B5="Agree"))

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. I had just looked into the Pivot Table and it's working the way I need it to!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.