Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm doing some basic survey analysis. I want to break down responses to various questions based on Male or Female. Each row is a response, each column is a question. Column A has the Male/Female response that I want to sum all other responses against. Each other column has various responses to that question. Different columns have different sets of possible responses, though some have the same. There are some empty cells that I would like to count as such.

I figured out how to do it for a single column with DataPilot. But as soon as I add more than one column into DataPilot, it gets messy.

If I could generate the equivalent of a table like this, that would be what I want:

           Male  Female
Q1  Yes    1     1
    No     0     1
    Maybe  1     0
Q2  1 Hr   2     1
    2 Hr   0     1

Is there a straightforward way to do that?

Input data for the above would be:

Respondent    Gender  Q1    Q2
Respondent1   M       Yes   1 Hr
Respondent2   M       Maybe 1 Hr
Respondent3   F       Yes   1 Hr
Respondent4   F       No    2 Hr
share|improve this question
I don’t understand your question. It might help if you showed us the input that you want to analyze. – Scott Dec 11 '12 at 21:02
@Scott added input data in question. – taotree Dec 12 '12 at 18:01
If nothing else, one cheap/dirty workaround would involve sorting your input data by gender and then running COUNTIF on the questions within each range. It's not simple or elegant, and it's not extensible, but if you know that your output data will not change, it can be extended for an arbitrary number of respondents without too much difficulty. – Jonathan Garber Dec 12 '12 at 18:56

I don’t have OO and appreciate it may be difficult with that to (i) Move fields up and down and (ii) show blanks as 0 in DataPilot, but since DataPilot seems a sensible approach I suspect it may be best to invest in some layout rearrangement first.

With the Q1, Q2 columns stacked, Gender repeated and an extra column (here A), I believe the result you want is achievable with a PivotTable (closer than my attempt in LibreOffice, though that result might be deemed acceptable, if Excel’s is):

SU518015 example

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.