Hi, I am trying to set up a crosstab table showing responses to survey questions. Some of the survey questions allow multiple responses ("Select all that apply"), so there can be a variable number of columns for a single question. See attached example. In this example, each row is a separate survey response, and columns C-F show the (possibly multiple) responses for a single question.
I need to count (for example) how many selected surveys (column A=1) with female respondents (column B = "F") chose response 2 as one of their responses (anywhere in columns C thru F). So this is kind of like a COUNTIFS, except the last range needs to span multiple (in this case, 4) columns. Number of choices can vary so that last range could be one or more columns for different questions. I need a general solution that works for any specified number of "answer" columns so I can't just string together COUNTIFS (for column C) + COUNTIFS (for column D) + COUNTIFS (for column E) etc.
COUNTIFS doesn't work with the multiple-column range, and I've tried a variety of array formulas, SUMPRODUCT, logicals, etc. with no luck. Any suggestions?
Bookmarks