I am trying to figure out formula using Power Pivot in Excel. Please come up with a formula that calculates the following.

There are three tables of data. The first table (Sales Table) has the sales price of various transactions and the zip code they were sold in. The second table (ZipCode) lists what state each zip code is in. The third table just lists the states that have sales.

I want to add a column in the third table that calculates the total sales amount for each state. I.e., if there are 5 sales in one zip code in AK and 4 in the other AK zip code, it will add the sum all of 9 AK sales, regardless of which AK zip code are sold in, and list the total sales for AK.

Thanks for your help!

V fix D E F G H Sales Total _ ZipCode ZipCode State V State V 34 99705 99705 AK AK 52 10152 10152 NY NY 24 99505 99505 AK 64 10465 10465 NY 25 99705 47 10152 42 99505 24 10465 75 99705 74 10152 24 99505 23 10465Sales ZipCode Table SalesTotal ZipCode State ZipCode State 1 T.Clipboard [Total] X V fx - S... Total Add Column 1 AK 2 NY