# Rank If/SumProduct complex formula

1. ## 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. ## 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

3. ## Re: Rank If/SumProduct complex formula

Bingo! Thank you so much!

4. ## Re: Rank If/SumProduct complex formula

You're welcome.

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

#### 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