0

Suppose I have this table:

| Name   | Amount | Category |
| name_1 | 10     | A        |
| name_2 | 2      | B        |
| name_3 | 3      | B        |
| name_4 | 6      | C        |

Thanks to this answer, I know how to write a query that groups the rows by category, and gives me sum of Amount by category, like this:

=QUERY(A2:C5,"select C, sum(B) group by C order by sum(B)")

enter image description here

My question is: how do I add a third row to my query, such that it lists the Name of every row in that group (possibly, but not necessarily, comma-separated)?

In other words, I'd like the result to look like this:

|   | sum | items          |
| B | 5   | name_2, name_3 |
| C | 6   | name_1         |
| A | 10  | name_4         |
1

Use filter(). Put this formula in cell C11:

=iferror( textjoin( ", ", true, filter(A$2:A$5, C$2:C$5 = A11) ) )

...and copy the formula cell down.

3
  • Doesn't seem to work, it seems the filter didn't find any matches. I've created a public scratch pad here: docs.google.com/spreadsheets/d/…
    – dcastro
    Jun 15 at 19:34
  • Right, you need replace the reference to column B with a reference to column C. Edited the answer. See your sample spreadsheet. Jun 15 at 19:54
  • Ah, brilliant, thank you :D
    – dcastro
    Jun 15 at 20:01

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.