+ Reply to Thread
Results 1 to 1 of 1

Formula to Calculate 2nd Most Common Text Item in List with Date Range Constraints

  1. #1
    Registered User
    Join Date
    03-22-2010
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    5

    Formula to Calculate 2nd Most Common Text Item in List with Date Range Constraints

    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!
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1