Jul 06 2020 11:11 AM
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.
Jul 06 2020 11:15 AM
Do you consider PivotTable as an option?
Jul 06 2020 11:18 AM
Not Pivot Table. I need a formula...
Jul 06 2020 11:28 AM
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
It's far less elegant than the Pivot Table, but it would get the answers.
Jul 06 2020 12:18 PM
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)))
Jul 06 2020 12:53 PM
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.
Jul 06 2020 10:50 PM
Thanks Sergei Baklan.
UNIQUE and FILTER are only in Office 365. Am using Office 2016. Not getting the exact results like you do.
Jul 07 2020 12:36 AM
Formula with SUMPRODUCT, you use it as array one, did you try it in your environment as regular (non-array) formula?
Jul 07 2020 04:27 AM