google-sheetssumalphabetical-sort

How do I take 2 tabs from a google sheet and combine based on the 1st column but also sum the 2nd column in Google Sheets?

Example

1st table:

Company Cost
A $100
B $200
C $50
D $100

2nd table:

Company Cost
A $50
D $75
A $90
D $100
B $200
D $100

I want combine the data in a new tab so that all data from company A is combined and the cost is summed. Same for company B, C, D. So in a new tab it would sum up the values for A ($100+$50+$90), B ($200+$200), C ($50), D ($100+$75+$100+$100). I know I can sort the data myself, but I want it to automatically combine/sum the data so if I add more data to the 1st or 2nd tab, it will already be combined/summed.
I also want it to be able to handle if I add a new random company at anytime.

I tried SUMIF but I don't know how to do that with unspecified labels for column A.

This is what I want it to look like in the end, but with it automatically calculating the amounts instead of me typing them in. And I want to be able to add Company E at any time and have it automatically added. https://docs.google.com/spreadsheets/d/1oB92nzVC3Dm9rwLPL-iOedWRc0zsSJYi3xjV9k5xZog/edit?usp=sharing

Solution

  • You can use QUERY. Assuming the first table is in A2:B and the second table is in D2:E, the formula would be:

    =QUERY({A2:B;D2:E},"select Col1, sum(Col2) where Col1 is not null group by Col1")