+ Reply to Thread
Results 1 to 3 of 3

Rank only if criteria is met, and omit null/zero values

  1. #1
    Registered User
    Join Date
    09-16-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    16

    Rank only if criteria is met, and omit null/zero values

    Hi.

    I've been pulling my hair out with this one.

    I have a column of values that I wish to rank. Let's say the values are profit made in a month for each operational site. However, there is another column which contains the state that that site is in. Finally, there is a column with actual name of the site. I have a massive list and eventually on another tab I am going to grab/lookup/match up the five sites with the worst performance (in this case, the lowest profit), listing their name.

    I want to be able to repeat this trick for other metrics (say, operating costs, number of OHS incidents etc.), always referring to another column with the state in it, so that I get the best/worst performers for each state.

    So if that isn't hard enough, I also wanted to be able to easily choose whether the highest or lowest number is the highest 'ranked' (i.e. in number 1 place, just like you can with a basic rank function) and also omit null values and 0 values.

    See attached sheet. Thinking about it now, if it's easier to just straight away get the Summary tab picking up things, rather than doing that intermediate step in the Data tab, then this would probably be better.

    Any ideas?

    I've been through so may iterations of rank, countif, countifs etc. Oh, and my sorting/filtering doesn't seem to be an option, so ignore this.

    Cheers!
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Rank only if criteria is met, and omit null/zero values

    Try this formula in Data sheet cell G2 copied down

    =IF(B2="NY",COUNTIFS(B$2:B$14,B2,C$2:C$14,"<>0",C$2:C$14,">"&C2)+1,"")

    Same formula for H2 except for "NY" changed to "CON"

    [Note: this ranks highest figure as 1, change ">" to "<" to reverse]

    Now in Summary sheet you can use this formula in C2 copied to C3

    =INDEX(Data!C$2:C$14,MATCH(1,INDEX((Data!$B$2:$B$14=A2)*(Data!$G$2:$G$14=B2),0),0))
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-16-2012
    Location
    Sydney
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Rank only if criteria is met, and omit null/zero values

    Mate, that's awesome. Thanks a lot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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