Hello I've data collected from google forms, with answers from the questions placed in those forms.

The data looks as follows the first column has a variety of answers from 20-25, 26-30, 31-35 and so on (which are the age groups). The second column has the answers yes or no

| your age | answer |
| 25-30    |  yes   |
| 25-30    |  no    |
| 41-45    |  yes   |
| 25-30    |  yes   |

The result I want to achieve is this

| grouped age | answered yes | answered no |
| 25-30       |  20          |  30         |
| 31-35       |  10          |  15         |

So the first column has the available age group answers, and the second and third one the count for each group based on the answer from the second column.

I've tried to do this according to this method How to group data in a Google Spreadsheet? but with no luck

The reason for this it to make a chart based on the age groups and their preferences.

  1. Besides of group by use pivot.

    =query(A:C,"select A COUNT(C) group by A pivot B")

    Note: query can't use an aggregation function an grouping/pivoting on the same column, so you should add an auxiliary column C having 1 for each row.

  2. To set a custom column header use label

For details see Query Language Reference (Version 0.7)

| improve this answer | |
  • Hmm, I get the same results but only in a different structure right now I'm trying =QUERY(odp!C2:H231;"select count(H) pivot C ") but it yields the same results as =QUERY(odp!C2:H231;"select (C), count(H) group by C") just horizontally – adam Apr 23 '18 at 19:15
  • Sorry instead of "instead" I should say besides. If you need further help add teh formulas that you tried to the question and a sample spreadsheet shared with anyone with the link. – Rubén Apr 23 '18 at 19:23
  • @adam I edited my answer. – Rubén Apr 23 '18 at 19:27
  • I'll try this. In the meantime i made a well not so elegant solution by using where since the are only two options in the second table (yes, or no) but i cannot get the second label to say 'yes' =QUERY(odp!C2:H231;"select (C), count(H) where H contains 'yes' group by C label C 'Age'") – adam Apr 23 '18 at 19:41
  • OK got it i know not the best solution since i had to change the query manually to get the results but maybe it will help someone =QUERY(odp!C2:H231;"select (C), count(H) where H contains 'yes' group by C label C 'Age', count(H) 'yes' ") – adam Apr 23 '18 at 19:56

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.