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


One solution would be to use countifs formulas

Yes to Both 2A and 3A


and all the other examples work the same.

A pivot table would be better but the data isn't set up correctly for that. First off it needs to be in columns (instead of rows) and second each question would be 1 column. So column A would be "Are you a boy or a girl" and the columns would all be filled in with either "boy" or "girl". Then you could see that there are 2 Girls in each age group and 2 Boys in groups 9,10, but only 1 in group 11.

enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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