Forum Discussion
Formula to summarize data in 2 tables
I have a table with 2 columns, city name and sales amount. There is another table with 2 columns city name and country name. Both tables are added to the data model and a relationship is connected fo...
There is a potential problem with the kind of relationship you describe, in that cities in different countries sometimes share names. That's definitely true in the United States, where many states have a city named "Springfield," for example.
That aside, the less elegant way to do this would be to
- use VLOOKUP to get the sales for each city attached to the City and Country on your second table.
- then SUMIF to get the totals for each country
It's far less elegant than the Pivot Table, but it would get the answers.