Concatenating from table to string, sum amounts

Question

Consider the Google Sheets table below:

       A             B            C        D
 1 category      subcategory    company  amount
 2 health care   diagnostics     AA       100
 3 health care   diagnostics     AB        50
 4 materials     mining          BA        75
 5 financials    banks           CA        30
 6 financials    insurers        CB        35
 7 financials    banks           CC        10
 8 financials    banks           CD        40
 9 financials    hedge fund      CE         5
10 health care   equipment       DA        50

I would like to list, per subcategory, the companies in it, and the amount spent:

      A              B              C
1 category       companies       amount
2 health care    AA AB DA          200     <--- 100 + 50 + 50
3 materials      BA                 75
4 financials     CA CB CC CD CE    120

Column A I will type myself / hardcode. But what formula in column B and C will give this result, depending on the value in A?

user_5632629 · Answer

try:

=ARRAYFORMULA(IFNA(VLOOKUP(E3:E, QUERY({QUERY({A:D}, 
 "select Col1,sum(Col4) 
  where Col1 is not null 
  group by Col1 
  label sum(Col4)'amount'", 1), {"companies"; 
 TRIM(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY({A:D, C:C}, 
 "select max(Col3) 
  where Col1 is not null 
  group by Col1 
  pivot Col5"), 
 "offset 1", 0)),,9^9)))}}, 
 "select Col1,Col3,Col2", 1), {2,3}, 0)))

where E3:E are your hardcoded values

Concatenating from table to string, sum amounts

1 Answers