Google-sheets – Grouping data as percentage or count based on responses and age groups

google sheets

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.

Best Answer

  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)

Related Topic