Hi,
I am looking to create a reporting dashboard for a workbook to track the referral categories of admissions to a medical facility.
I would like to create a user-selectable date range and then have a list of 30 or so cells below that would automatically populate with the 30 most common referral categories ranked in order along with how many times they appear. I know I could do this with a pivot table, but the people using this spreadsheet regularly do not have the excel skills to regularly update a pivot table, and they also have issues with macro permissions, so I would like to do this without VBA or pivot tables.
I have created a SUMPRODUCT formula that will give me the number of times a referral category is listed within a given date range, if I supply the date range and the category name. However, I don't know where to begin to try to get it to list the rank of the count of the given category. I know the LARGE function will allow me to calculate the 2nd largest, 3rd largest, etc, but MODE will not work with text, so I don't know how to see which appear most often.
I have attached a spreadsheet with a sample table, as well as my SUMPRODUCT formula (green headers) and the data I am looking for (orange headers).
Thanks!
Bookmarks