Search code examples
excelsum

How to find highest sum of multiple items in Excel

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?

enter image description here

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

Solution

  • 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])