Rank text cells uniquely with multiple criteria

[cpaul212]

How can I rank my rows uniquely without duplicates and with multiple column-criteria?

I have a Google spreadsheet with Col D, E, F, and each column is related. Col E is a sub-category of Col D, Col F is a sub-category of Col E.

These columns contain text and emoji. They are not sorted. I want to rank them (sans emoji, only text) uniquely. The ranking would be based on alphabetical sort and duplicate text values rank uniquely.

For example:

Text	Rank
A	1
A	2
B	3
C	4
C	5

I have been able to rank based on one column, like the chart above, with these 3 formulas.

=COUNTIF($D$2:$D$1230,"<"&D2)+(COUNT($D$2:$D$1230)*ISTEXT(D2))+COUNTIF($D$2:D2,D2)

=If(D2<>"", (COUNTIF($D$2:$D$1230, "<"&D2) + (COUNT($D$2:$D$1230) * ISTEXT(D2)) + COUNTIF($D$2:D2,D2)) -8, "0")

=COUNTIF($D$2:$D$1230,"<"&D2)+COUNTIF($D$2:D2,D2)-8

They work with one column. I want to rank based on 3 columns. I want the ranking to start with Col D and continues across the row to Col E and F.

For example:

Column D	Column E	Column F	Rank
A	Billy	jazz	1
A	apple	honey	2
B	Toyota	car	3

When I tried it to expand the formula to add Col E and F as criteria, none of the formulas worked.

The only one that came close was this one. But its alphabetical sort was not good.
=(COUNTIFs($D$2:$D$1230,"<="&D2, $E$2:$E$1230,"<="&E2, $F$2:$F$1230,"<="&F2) + (COUNT($D$2:$D$1230,$E$2:$E$1230,$F$2:$F$1230) * ISTEXT(D2:F2)) +COUNTIFS($D$2:D2,D2, $E$2:E2,E2, $F$2:F2,F2)) -4

And this formula duplicated the ranks of rows with the same categories (a.k.a criteria)
=(COUNTIFs($D$2:$D$1230,"<"&D2, $E$2:$E$1230,"<"&E2, $F$2:$F$1230,"<"&F2) +(COUNT($D$2:$D$1230,$E$2:$E$1230,$F$2:$F$1230) * ISTEXT(D2:F2)) +COUNTIFS($D$2:D2,D2, $E$2:E2,E2, $F$2:F2,F2))-2

Again, my data is not sorted. Here is a sample spreadsheet with unsorted data to show what I'm looking for.

WHAT I WANT: RANK #	CATEGORY 1	CATEGORY 2	CATEGORY 3
4	fruit	red	apple
5	?fruit	red	strawberry
6	?fruit	yellow	banana
11	vegetable	yellow	bell pepper
10	vegetable	?red	tomato
8	meat	poultry	chicken
9	?meat	poultry	turkey
1	?dairy		ice cream
3	?dairy		yogurt
2	?dairy		milk
7	meat	?fish	salmon

 

[cpaul212]

Correction: with this formula, the ranking starts over again with whenever a column has new text/category.
=(COUNTIFs($D$2:$D$1230,"<"&D2, $E$2:$E$1230,"<"&E2, $F$2:$F$1230,"<"&F2) +(COUNT($D$2:$D$1230,$E$2:$E$1230,$F$2:$F$1230) * ISTEXT(D2:F2)) +COUNTIFS($D$2:D2,D2, $E$2:E2,E2, $F$2:F2,F2))-2

 

[Sulprobil]

=SUMPRODUCT(--(D2>$D$2:$D$1230))+SUMPRODUCT(--(D2=$D$2:$D$1230),--(E2>$E$2:$E$1230))+SUMPRODUCT(--(D2=$D$2:$D$1230),--(E2=$E$2:$E$1230),--(F2>$F$2:$F$1230))+SUMPRODUCT(--(D2=$D$2:D2),--(E2=$E$2:E2),--(F2=$F$2:F2))

 

[cpaul212]

=SUMPRODUCT(--(D2>$D$2:$D$1230))+SUMPRODUCT(--(D2=$D$2:$D$1230),--(E2>$E$2:$E$1230))+SUMPRODUCT(--(D2=$D$2:$D$1230),--(E2=$E$2:$E$1230),--(F2>$F$2:$F$1230))+SUMPRODUCT(--(D2=$D$2:D2),--(E2=$E$2:E2),--(F2=$F$2:F2))

It worked!! Thank you!