CODEWITHSUNDEEP
arraysexcelexcel-formulamax
Marmiketin
Marmiketin

Reputation: 177

Excel - Is there a way to SUM values based on 2 associated criteria and then find the MAX summed value?

I have a list of buildings by city and building name which all have 5 items associated to them and each have a monetary value. I am trying the sum these 5 values based on the city and building name and then of those sums I want to output the maximum value.

enter image description here

I have tried using this code in the Value Cell F4 from the image but it only returns the sum of the first 5 items from the table.

=MAX(SUMIFS(Table1[Value],Table1[Location],INDEX(Table1[Location],,1)))

I am not sure how to store a list of all the summed values of the different buildings and then output the max value.

I would also like the City and Building name associated to the maximum summed value be output in the other 2 cells G4 and H4

Upvotes: 1

Views: 1114

Answers (3)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60199

You can also do this using Power Query.

This would be particularly useful in the event two buildings have the same value, as I have adjusted the data below to show.

  • Group the rows by Location and Building
    • Aggregating by Sum
  • Filter the rows by the Maximum of the Values column
  • Do some rearranging and renaming

Except for the Filter operation, which needs to be entered manually in the Advanced Editor, all of the operations can be done using the Power Query GUI.

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Building", type text}, {"Item", type text}, {"Value", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Location", "Building"}, {{"Total Values", each List.Sum([Value]), type number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Total Values] = List.Max(#"Grouped Rows"[Total Values]))),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Total Values", "Location", "Building"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Total Values", "Value"}})
in
    #"Renamed Columns"

Source Data

enter image description here

Max Values

enter image description here

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34190

For the max sum

=MAX(SUMIFS(Table1[Value],Table1[Location],Table1[Location],Table1[Building],Table1[Building]))

For the City

=INDEX(Table1[Location],MATCH(F4,SUMIFS(Table1[Value],Table1[Location],Table1[Location],Table1[Building],Table1[Building]),0))

and for the Building

=INDEX(Table1[Building],MATCH(F4,SUMIFS(Table1[Value],Table1[Location],Table1[Location],Table1[Building],Table1[Building]),0))

all entered as array formulas using CtrlShiftEnter

enter image description here

Upvotes: 2

J. Paulo
J. Paulo

Reputation: 16

There's an easy workaround to that. If it's not a problem, you can add a column to your table with the sum of that building/location

=SUMIFS(Table1[Value],Table1[Location],[@[Location]],Table1[Building],[@[Building]])

(Something like this in your table)

Example

And then do the MAX of that new column

Edit:

By the way, you can also do that with a pivot table, and filter the top 1 value of the sum

Upvotes: 0

Related Questions