Return the Cell(s) with the Highest SUM() between groups of Cells

I finally was able to take a look at all these! Thank you all again!! I've got a lot of functions to learn and understand now 😆Plus, the variety of answers gives me ideas how to make functions in the future.

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

Below is the Power Query M code that produces the green result table.

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Branch", type text}, {"Region", type text}, {"Points", Int64.Type}}),

// determine top-branch
BranchTotals = Table.Group(#"Changed Type", {"Branch"}, {{"TotalPoints", each List.Sum([Points]), type number}}),
BranchSorted = Table.Sort(BranchTotals, {{"TotalPoints", Order.Descending}}),
BranchTopName = BranchSorted{0}[Branch],
BranchTopPoints = BranchSorted{0}[TotalPoints],

// determine top-region 
RegionTotals = Table.Group(#"Changed Type", {"Region"}, {{"TotalPoints", each List.Sum([Points]), type number}}),
RegionSorted = Table.Sort(RegionTotals, {{"TotalPoints", Order.Descending}}),
RegionTopName = RegionSorted{0}[Region],
RegionTopPoints = RegionSorted{0}[TotalPoints],

// combine result
Result = Table.FromRecords({
    [Top Branch=BranchTopName, Top Branch Total=BranchTopPoints, Top Region=RegionTopName, Top Region Total=RegionTopPoints]
}),
    #"Demoted Headers" = Table.DemoteHeaders(Result),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1")
in
    #"Transposed Table"

These are all beautiful!! Thank you all so much! Especially for explanations on why they work and suggestions. I haven't had the chance to go through all yet, but I'm hoping to tomorrow or Wednesday. I don't have access to my doc right now.

My preference would be to aggregate by branch and region as two distinct calculation (unless you require a top branch per region).

= TAKE(GROUPBY(Table1[branch],Table1[points],SUM,,0,-2),1)

= TAKE(GROUPBY(Table1[region],Table1[points],SUM,,0,-2),1)

It is possible to obtain both sets of values as  total columns in a PIVOTBY but that requires effort sorting out the top row sum and column sum whilst avoiding the grand total.

Other way:

=LET(g,LAMBDA(x,GROUPBY(x,Table25[Points],SUM,0,0)),city,g(Table25[Branch]),region,g(Table25[Region]),c,CHOOSECOLS,VSTACK(XLOOKUP(MAX(c(city,2)),c(city,2),c(city,{1;2})),XLOOKUP(MAX(c(region,2)),c(region,2),c(region,{1;2}))))

return:

Bye

Hi LilyB​,

Try this formula in cell F5:

=LET(
     data, Table25[#All],
           TAKE(PIVOTBY(CHOOSECOLS(data, 2), TAKE(data,, 1), TAKE(data,, -1), SUM,,, -2,, -2), 2)
)

and this formula in cell F9:

=LET(
      data, Table25[#All],
     p_tbl, PIVOTBY(CHOOSECOLS(data, 2), TAKE(data,, 1), TAKE(data,, -1), SUM,,, -2,, -2),
      drop, DROP(DROP(p_tbl, 1, 1), -1, -1),
     m_row, MAX(BYROW(drop, SUM)),
     m_col, MAX(BYCOL(drop, SUM)),
            HSTACK({"Top Region:";"Top Region Total:";"Top Branch:";"Top Branch Total:"},
                   VSTACK(XLOOKUP(m_row, BYROW(drop, SUM), TAKE(DROP(DROP(p_tbl, 1), -1),, 1)), m_row,
                          XLOOKUP(m_col, BYCOL(drop, SUM), TAKE(DROP(DROP(p_tbl,, 1),, -1), 1)), m_col))
)

HTH

IlirU

There are lots of answers but here is one:

you can use PIVOTBY to do the grouping and totalling and then pull off those top values.  In the below I just show the top result from the PIVOTBY which is the Top Region but it does show all the Branches (I could easily trim off the middle non-top branches but the resulting output may be confusing.  But using that ouput I added some simple INDEX to pull of the individual values to output the table you requested:

here is the first formula (cell F2):

=TAKE(PIVOTBY(Table1[region],Table1[branch],Table1[points],SUM,,,-2,,-2),2)

here is the final formula (cel F6) which is also shown in the formula bar in the image:

=LET(a,TAKE(PIVOTBY(Table1[region],Table1[branch],Table1[points],SUM,,,-2,,-2),2),
         HSTACK({"Top Region:";"Top Region Total:";"Top Branch:";"Top Branch Total"},
                       VSTACK(INDEX(a,2,1),TAKE(a,-1,-1),INDEX(a,1,2),INDEX(a,2,2))))