How to find highest sum of multiple items in Excel-CodePudding

Let's say we have a table like this in excel. How we can formulate finding the office with the highest total cost in the database?

I would also appreciate if you also know the answer in SQL syntax (in addition to Excel). (let's call this database dbo.cost

Thanks

CodePudding user response：

A couple of options for Excel:

Office 365:

=INDEX(SORT(CHOOSE({1,2},SUMIFS(Table1[Cost],Table1[Office],Table1[Office]),Table1[Office]),,-1),1,2)

All versions:

=LOOKUP(1,0/FREQUENCY(0,1/(1 SUMIFS(Table1[Cost],Table1[Office],Table1[Office]))),Table1[Office])

CodePudding user response：

Try the following for the SQL part (with SQL Server):

SELECT TOP 1 office, sum(cost) as Total  from Costs
GROUP BY Office
ORDER BY Total DESC