9

I'm running a QUERY with a SUM and GROUP BY, but I'd like to aggregate multiple distinct values from the rows into a single row and column. I'm looking to concatenate all those values together.

Current Table:

Person Widget Count
Bill Red 12
Bill Blue 9
Sarah Yellow 4
Bill Yellow 1
Sarah Orange 10

Expected Table:

Person Widget Count
Bill Red, Blue, Yellow 22
Sarah Yellow, Orange 14
Nimantha
  • 6,405
  • 6
  • 28
  • 69
Tom
  • 1,051
  • 4
  • 21
  • 36

1 Answers1

18

You can use the filter and join functions to help:

To get a unique list of names:

=UNIQUE(A3:A)

To join the widgets:

=join(",",filter(B:B,A:A=E3))

To sum the values:

=sum(filter(C:C,A:A=E3))

enter image description here

Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
  • 2
    Anyway to make the JOIN and SUM an array formula? So as new Person, Widget and Count is added, the table updates automatically? – Harry Norman Apr 06 '21 at 16:36
  • This is amazing. I wish Google Sheets and Microsoft Excel could add this in their Pivot table's summary function, just like Splunk. – Naveed Ul Islam Apr 14 '22 at 02:26