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.

1 Answer 1

=QUERY(A:B,"Select B,sum(A) where B is not null group by B",1)
  • Thank you very much! I did not know QUERY exists. Commented May 19, 2018 at 13:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.