Formula to summarize data in 2 tables

New Contributor

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 for city name in both tables.


I want to write a formula to find out total sales in one specific country. How do I do this? Solution can be with or without using data model. Just need the formula result to come correctly.

8 Replies


Do you consider PivotTable as an option?

@Sergei Baklan 

Not Pivot Table. I need a formula...



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

  1. use VLOOKUP to get the sales for each city attached to the City and Country on your second table.
  2. then SUMIF to get the totals for each country


It's far less elegant than the Pivot Table, but it would get the answers.


For such sample


list of countries is


and Sales per country




Just in vase, if dynamic arrays are not available


to select the country


Sales for it


and drag both cells down till empty one appear.

@Sergei Baklan 


Thanks Sergei Baklan.

UNIQUE and FILTER are only in Office 365. Am using Office 2016. Not getting the exact results like you do. 


Formula with SUMPRODUCT, you use it as array one, did you try it in your environment as regular (non-array) formula?

I tried both ways... Didn't get correct result...