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...
Do you consider PivotTable as an option?
Not Pivot Table. I need a formula...
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.
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?
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)))