How to chart a sum of categorised values

Question

I'm trying to figure out how to chart this in Google Sheets. Each segment of the pie chart should be the sum of each different "Type", e.g. there should only be one pie segment per type.

I tried clicking "Aggregate" but it made the chart go blank.

Answer 1

You can aggregate the data by putting this formula in a cell in a free area of the spreadsheet:

=query(C1:D, "select C, sum(D) where C is not null group by C", 1)

Then select the whole result table and choose Insert > Chart.

If the result table has multiple occurrences of "Entertainment", you probably have differences in the spacing of the values in column C, and will need to use this longer formula instead:

=arrayformula( 
  query( 
    { trim(C1:C), D1:D }, 
    "select Col1, sum(Col2) where Col1 is not null group by Col1", 
    1 
  ) 
)