Ranking with sumproduct without duplicates

[luke11111]

D P V

product sales rank

aaaa11 63 1
aaaa11 12 3
aabb11 22 1
aabb11 22 1
aacc11 14 1
aadd11 66 1
aaaa11 45 2
aabb11 22 1

I have already ranked by product using sumproduct =SUMPRODUCT(--($D$1:$D$43354=D1),--(P1<$P$1:$P$43354))+1
but it returns duplicates and messes up other formulae i have running. how can i get it to just assign aabb11 to go 1,2,3 i dont care which ones.
There is a profit% column(S) which i tried to rank if P=P:P but it didnt work.
Can anyone help?

[benishiryo]

hi luke11111. since you don't mind which comes first, try:
=SUMPRODUCT(($D$1:$D$43354=D1)*($P$1:$P$43354+ROW($P$1:$P$43354)/10000>=P1+ROW(P1)/10000))

Edit: in an opposite way, this would probably work as well
=SUMPRODUCT(($D$1:$D$43354=D1)*($P$1:$P$43354-ROW($P$1:$P$43354)/10000>=P1-ROW(P1)/10000))

[luke11111]

Hello and Thank you. Just tried it and it returns #VALUE!
Not sure why. Thanks anyway.

[luke11111]

I cant understand how those formuale work on the sample but when I copy and paste them into the main data,
one returns a #VALUE! and the other does rank but the numbers are too high. Doesnt seem to be any ranks 1-50
Any Ideas?
Thanks again for your time

[luke11111]

Thank you so much. Ive just gone through my data and found that a header had got mixed up in it.
This was messing up all my formulae. Lesson for the future.
Your formula's work like a charm. Thank you my friend

[Hercules1946]

Hello Luke
You may like to try the Evaluate formula option on the formula group. This is brilliant for finding exactly whereabouts the error is occurring. It might just be a typo, or something thats
referring to the wrong cell.

HTH

[oeldere]

Maybe like this (a pivot table)