google-sheetsgoogle-sheets-formulagoogle-query-language

Count values in multiply columns as in single one, group by other column


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

Solution

  • 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)
    

    enter image description here