Unique Ranking with Multiple Criteria

**dglenski** · 05-08-2017, 10:53 PM

Hi,

Using the formula below, I was able to rank using multiple criteria, but this formula spits out duplicate ranks (1,1,3) and I need it to shoot out unique ranks (1,2,3).

=SUMPRODUCT((B3=B$3:B$9)*(C3<C$3:C$9))+1

Data Set:
Berry 12/31/2017
Orange 3/22/2017
Apple 3/31/2017
Berry 3/22/2017
Apple 6/30/2017
Apple 6/30/2017
Orange 3/22/2017
Orange 4/18/2017

Is there a solution for this?

**FlameRetired** · 05-08-2017, 11:22 PM

Try a helper column to identify the unique items with a formula like this in column D.

=IF(COUNTIFS($B$3:B3,B3,$C$3:C3,C3)=1,ROWS($3:3),"")

Can you do your ranking from that point?

	A	B	C	D
1
2
3		Berry	12/31/2017	1
4		Orange	3/22/2017	2
5		Apple	3/31/2017	3
6		Berry	3/22/2017	4
7		Apple	6/30/2017	5
8		Apple	6/30/2017
9		Orange	3/22/2017
10		Orange	4/18/2017	8

**dglenski** · 05-08-2017, 11:51 PM

I don't think this is helpful because I am trying to assign a rank to each category using the date. Ultimately, I am trying to make a unique identifier by combining the category and its rank in the category.

For example: apple1, apple2, apple3, orange1, orange2, ect.

**FlameRetired** · 05-09-2017, 12:03 AM

My formula was only intended to identify the unique combinations to enable you to apply a ranking formula you might have in mind.

If you need a ranking formula too I believe we are going to need an upload Excel file (*.xlsx) with explicit BEFORE - AFTER sections (hand typed if necessary) and an explanation of the logic needed to arrive at those rankings.

If you are not familiar with how to do this:

To attach a file to your post,

click “Go Advanced” (next to Post Quick Reply – bottom right),
scroll down until you see “Manage Attachments”, click that,
click “Browse”.
select your file(s)
click “Upload”
click “Close window”
click “Submit Reply”
be sure to desensitize the data

The file name will appear at the bottom of your reply.

BTW This is a duplicate thread.

https://www.excelforum.com/excel-for...ml#post4649627

**dglenski** · 05-09-2017, 12:23 AM

In column D, I have used the formula (=SUMPRODUCT((B4=B$3:B$10)*(C4<C$3:C$10))+1) to calculate a rank by category based on the date. If the date is the same, the formula will spit out a duplicate rank (see D7 and D8). I need a formula that will spit out unique ranks (D7 to be 1 and D8 to be 2). I have hardcoded the answers to this small set in column E.