+ Reply to Thread
Results 1 to 4 of 4

Rank If/SumProduct complex formula

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    Charlottesville, VA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    2

    Rank If/SumProduct complex formula

    Hey All,

    I am new to this forum. I am developing a report that requires me to rank users by their usage within a given company. I have looked at other threads, but I couldn't get the solutions to apply to my particular question. A sample from my data set:

    1620I13CB5 tjohnston (1000323843) 7.00 1
    1CINA1D209 chris (1000316692) 45,764.16 1
    1STAA1CE71 dchristian (1000321065) 1,518.39 1
    1STAA1CE71 dfalin (1073844954) 68.03 1
    1STAA1CE71 mhiggins (1000343906) 15.00 1
    1STAA1CE71 jsargent (1000310202) 557.33 1
    1STAA1CE71 ewignall (1073844958) 134.34 1

    The ranks should read 1, 1, 1, 4, 5, 2, 3. I tried using a rank if formula but I always got 1 for all lines. I tried using sumproducts and I got 1 for all lines as well. I am a little stumped so if anyone has suggestions that would be most helpful. Thank you so much!

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Rank If/SumProduct complex formula

    Hi,

    Assuming the data you give above is in A1:C7, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in D1 and copy down as required:

    =MATCH(C1,LARGE(IF($A$1:$A$7=A1,$C$1:$C$7),ROW((INDIRECT("1:"&COUNTIF($A$1:$A$7,A1))))),0)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    06-28-2013
    Location
    Charlottesville, VA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    2

    Re: Rank If/SumProduct complex formula

    Bingo! Thank you so much!

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Rank If/SumProduct complex formula

    You're welcome.

+ 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