Forum Discussion
yusufalikk
Jul 06, 2020Copper Contributor
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...
yusufalikk
Jul 06, 2020Copper Contributor
Not Pivot Table. I need a formula...
SergeiBaklan
Jul 06, 2020Diamond Contributor
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.
- yusufalikkJul 07, 2020Copper Contributor
Thanks Sergei Baklan.
UNIQUE and FILTER are only in Office 365. Am using Office 2016. Not getting the exact results like you do.
- SergeiBaklanJul 07, 2020Diamond Contributor
Formula with SUMPRODUCT, you use it as array one, did you try it in your environment as regular (non-array) formula?
- yusufalikkJul 07, 2020Copper ContributorI tried both ways... Didn't get correct result...