Excel worksheet Top ten items from list basted on total column

Hello everyone!
I'm attaching a sample file of what I am trying to accomplish. I have a list that of expenses that is generated from an accounting program, we want to show a list of Top 10 or 20 biggest expenses. Is there a formula that would allow me to automatically go through the list in Sheet 1 and then give me at the bottom or in a Sheet the same information by month but only capture the top 10 Expenses. I know I can sort this every time I re-run the worksheet but I was hoping to automate this process as much as possible.

Thank you!!
Sample.xlsx
joeserroneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
redmondbConnect With a Mentor Commented:
Hi, joeserrone.

Please see the attached. I'm afraid the formulas aren't exactly straightforward (each column's formulas are array-entered), so please ask if you want an explanation.

Regards,
Brian.
Sample--2----V2.xlsx
0
 
redmondbConnect With a Mentor Commented:
joeserrone,

The attached is much more straightforward, if somewhat less elegant.

Regards,
Brian.
Sample--2----V3.xlsx
0
 
cbenConnect With a Mentor Commented:
joeserrone

In addition to redmond's great solution you might want to define the category of the expenses in the list.

See attached.

Regards
10-highest-from-list.xlsx
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
redmondbConnect With a Mentor Commented:
Excellent idea, cben!

A small point - if two categories are for the same amount then the first category is shown twice.  That's most likely to occur for zero amounts so, I've made the following changes in the attached...
(1) Don't show a category for zero amounts.
(2) On the extremely rare (I assume) cases where a non-zero amount is duplicated, just highlight the problem. (If these clashes are not "extremely rare" then a proper solution is required!)

Regards,
Brian.10-highest-from-list-V2.xlsx
0
 
joeserroneAuthor Commented:
Thank you for your help
0
 
redmondbCommented:
Thanks, joeserrone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.