Google Sheets Formulas – Using UNIQUE and SUM for All Possible Values

formulasgoogle sheets

I have a much bigger and more serious table just like this one:

enter image description here

As you can see there are more possible category than I use.

What I want to achieve is listing all the categories (even ones that does not currently in use) and SUM the values corresponding with the categories.

I would like to see this for every category automatically:

enter image description here

So far I tried to use UNIQUE(B2:B) but it only lists categories in use (it makes sense though) Also I done this: =SUM(FILTER(A2:A;B2:B = G2)) and copied it downwards. It SUMs all categories fine, but I have to copy to formula by hand at every new category.

So my sheet looks like this now:

enter image description here

Question: Is there a way to do this fully automatically? Same as =UNIQUE() generates a list automatically in a column it would be nice to generate the SUMs next to it in one formula. Because as new categories come I have to manage the SUM column by hand every time I notice it has no calculation for given category.

Best Answer

=QUERY(A:B,"Select B,sum(A) where B is not null group by B",1)