There is a table with questionary responses. One column (e. g. B) represents respondent's age group (e. g. teenagers / adults). Some other sequental columns contain their answers for a single question (1 to 4 variants are acceptable; note that a certain column IS NOT related to a certain variant). So I would like to count quantities of each variant selected and group them first by respondent's age group and second by variant itself. How can I achieve that?
In case of single column of answers (e. g. Z) the query looks like:
=QUERY(B:Z; "SELECT B, Z, COUNT(Z) GROUP BY B, Z LABEL B 'Age Group', Z 'Variant', COUNT(Z) 'Quantity'"; 1)
How should I change it if answers are in W…Z?
UPD: Minimal reproducible example
Input:
A | B | W | X | Y | Z |
---|---|---|---|---|---|
Name | Age Group | Answers -> | |||
Alice | Teen | Banana | Apple | Plum | |
Bob | Adult | Mango | |||
Claire | Adult | Pineapple | Mango | ||
David | Teen | Apple | Mango | Carrot | Orange |
Expected output:
Age Group | Variant | Quantity |
---|---|---|
Adult | Mango | 2 |
Adult | Pineapple | 1 |
Teen | Banana | 1 |
Teen | Apple | 2 |
Teen | Plum | 1 |
Teen | Mango | 1 |
Teen | Carrot | 1 |
Teen | Orange | 1 |
Here's a generalized approach which you may adapt accordingly:
=query({{B2:B,W2:W};{B2:B,X2:X};{B2:B,Y2:Y};{B2:B,Z2:Z}},"Select Col1,Col2,count(Col2) Where Col2!='' group by Col1,Col2 label count(Col2) ''",0)