This is a bit of a complicated question.. so bear with me!
I have a spreadsheet which I've set up as a tally chart - for ease of inputting paper-based data.
The D
column has a relatively simple formula to count the entries from my questionnaires: =IF(ISBLANK($C7),"",COUNTIF($E7:$GA7,1))
.
Each column after D
, i.e. E
, F
and so on up to AZ
represents one questionnaire. So, if the column contains a 1
in Row 3
, it means that questionnaire's response was "I am a girl".
As an example, the below questionnaire in row F
would have answered "1A: Boy", "1B: Year 9", "1C: (cell C7) Secret Middle School", "1D: Never attended any other high school", "2A: Have been bullied", "2D: In middle school" etc.
Now... what I'm hoping to achieve, is analysis of the data by certain questions:
- How many people said yes to both 2A and 3A
- If 2A is "Yes" then count the number of 2D's options (Middle, High, Both)
- How many people said yes to both 2A and 4A
- How many people said yes to 2A but no to 4A
I don't even know where to start. Could someone please point me in the right direction? I'm fairly comfortable with Excel formulae but I haven't used a lot of VLOOKUP
or Pivot Tables.
Thanks in advance, Duncan