# Using the Sumproduct formula to rank a group within a list-how to i adapt it to break tie?

1. ## Using the Sumproduct formula to rank a group within a list-how to i adapt it to break tie?

Hi all, i am using the following formula Sumproduct((\$a\$2:a\$10702=a2)*(\$c2:\$c\$10702>c2))+1
Column A is the groups and Column C is the \$ sales that i want ranked within these groups. I am doing an index match function to pull the customer (column B) on anther sheet by rank, but it returns blank for 3rd place when two customers are tied for 2nd.
Thanks all, any help on this would be appreciated.

2. ## Re: Using the Sumproduct formula to rank a group within a list-how to i adapt it to break

Post about 10 rows worth of data and show us what results you expect.

3. ## Re: Using the Sumproduct formula to rank a group within a list-how to i adapt it to break

• A. B. C D - rank
• 4991406. Bob's garage. \$50.00. 2
• 4991406. Ted's garage. \$75.00. 1
• 4991406. Greg's garage. \$50.00. 2
4991584. Evan's garage. \$ 25.00. 1

On another spreadsheet i have a drop down list for the dealer number (column A) with a top 10 list numbered 1 thru 10. I have an index match function to return the customer(column B) based on rank. Because 4991406 has two dealers ranked at 2 it returns 0 as the customer name for the 3rd rank. I am fine with it returning the first customer that is ranked as 2 and the other customer as 3rd.
Hope this explains it properly.

4. ## Re: Using the Sumproduct formula to rank a group within a list-how to i adapt it to break

Are these the results you want:

Data Range
 A B C D 1 Header1 Header2 Header3 Header4 2 4991406 Bob's garage 50 2 3 4991406 Ted's garage 75 1 4 4991406 Greg's garage 50 3 5 4991584 Evan's garage 25 1

This array formula** entered in D2 and copied down:

=SUM(IF(A\$2:A\$5=A2,IF(C\$2:C\$5>C2,1)))+SUMPRODUCT(--(A\$2:A2=A2),--(C\$2:C2=C2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

5. ## Re: Using the Sumproduct formula to rank a group within a list-how to i adapt it to break

That's the ticket! Thanks a bundle Tony. Hope I can pay it forward soon.

6. ## Re: Using the Sumproduct formula to rank a group within a list-how to i adapt it to break

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

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