Forum Discussion

yusufalikk's avatar
yusufalikk
Copper Contributor
Jul 06, 2020

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

excel

8 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jul 06, 2020

    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.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 06, 2020

        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.

Resources