+ Reply to Thread
Results 1 to 4 of 4

Display top five values that meet criteria

Hybrid View

themob212 Display top five values that... 08-07-2015, 10:31 AM
davesexcel Re: Display top five values... 08-08-2015, 05:35 AM
themob212 Re: Display top five values... 08-11-2015, 07:42 AM
JeteMc Re: Display top five values... 08-11-2015, 02:29 PM
  1. #1
    Registered User
    Join Date
    10-29-2014
    Location
    Uk
    MS-Off Ver
    2007 and 2013
    Posts
    16

    Display top five values that meet criteria

    Hey all, been struggling with a tricky problem and google doesn't seem to be helping.

    Basically I want to take the top five most important goals as from one table, but only ones that meet the requirement "Mine"

    The table is setup on the worksheet Goals as follows
    Colum B stores the goals
    Colum C the priority of the goal (1-5) so it needs to be okay with duplicates
    Colum F who the goal belongs to (Mine or others)

    The table its transferring to is on another sheet and has the following formula stored in C3
    =IFERROR(INDEX(Goals!$B$2:$B$100,MATCH(1,INDEX((Goals!$C$2:$C$100=LARGE(Goals!$C$2:$C$100,ROWS(C$5:C5)))*(COUNTIF(C$5:C5,Goals!$B$2:$B$100)=0),),0)),"")

    This works just fine to get me the top five values, I just don’t know how to make it based on criteria from colum F

    Any help would be very gratefully received

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,524

    Re: Display top five values that meet criteria

    Attaching a sample workbook enables others to work on your problem:

    To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

    To add a file to a new post

    To add a file to an existing post.

  3. #3
    Registered User
    Join Date
    10-29-2014
    Location
    Uk
    MS-Off Ver
    2007 and 2013
    Posts
    16

    Re: Display top five values that meet criteria

    I have attached a copy of what I am trying to do- as I said before I just need to be able to add a criteria to the sorted worksheet- so the top five priority items which have the criteria "Staff".

    Any help would be superb!

    Thanks
    Ben
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 365 version 2501
    Posts
    18,859

    Re: Display top five values that meet criteria

    This formula works for the file that you provided:
    =IF(ROW()-4>COUNTIFS(Goals!F$2:F$7,"Mine"),"",INDEX(Goals!B$2:B$7,LARGE((Goals!F$2:F$7="Mine")*ROW(Goals!C$2:C$7)-1,ROW()-4)))
    I based the formula on your first post which said that you wanted to limit the results to goals listed as "Mine".
    Let me know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. how to add all values above row that meet multiple criteria criteria
    By skikio in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-18-2015, 09:40 AM
  2. Replies: 0
    Last Post: 04-25-2015, 01:14 AM
  3. Display (different) warnings if any cells in certain columns meet criteria
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-31-2013, 09:57 AM
  4. Sum values in cells to meet a certain criteria
    By kwillis33 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 08-29-2013, 09:53 AM
  5. Display a list of people who don't meet certain criteria in a new work sheet.
    By joshnathan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2012, 06:52 AM
  6. Replies: 5
    Last Post: 09-06-2011, 10:44 AM
  7. Totalling up values that meet two criteria
    By johnmitch38 in forum Excel General
    Replies: 5
    Last Post: 06-04-2009, 05:49 AM

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