Formula to summarize data in 2 tables

[yusufalikk]

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.

[#2]

@yusufalikk 

Do you consider PivotTable as an option?

[#3]

@Sergei Baklan 

Not Pivot Table. I need a formula...

[#4]

@yusufalikk 

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.

[#5]

@yusufalikk 

For such sample

list of countries is

=UNIQUE(tblCountry[Country/region])

and Sales per country

=SUM(SUMIFS(tblSales[Sales],tblSales[City],FILTER(tblCountry[City],tblCountry[Country/region]=J3)))

[#6]

@yusufalikk 

Just in vase, if dynamic arrays are not available

to select the country

=IFERROR(INDEX(tblCountry[Country/region],
     AGGREGATE(15,6,
        1/(COUNTIFS($M$2:$M2,tblCountry[Country/region])=0)*
        (ROW(tblCountry[Country/region])-ROW(tblCountry[[#Headers],[City]])),
        1)),"")

Sales for it

=SUMPRODUCT(
     SUMIFS(tblSales[Sales],tblSales[City],tblSales[City])/
     COUNTIFS(tblSales[City],tblSales[City])*
     (INDEX(tblCountry[Country/region],MATCH(tblSales[City],tblCountry[City],0))=M3)
)

and drag both cells down till empty one appear.

[#7]

@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. 

[#8]

@yusufalikk 

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

[#9]

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