Rank By Category and Not Skip Values

[aoballer008]

Hello All,

I have a big deliverable due at work today and I am having the most horrendous time with my sumproduct formula for rank. I need a formula that can provide a rank by category and not skip rank values.

=SUMPRODUCT(--($E2=E$2:E$100),--($AV2<AV$2:AV$100)/COUNTIF(AV$2:AV$100,AV$2:AV$100&""))+1)

E is my category column

AV has the values I need to rank.

I'm getting errors or just numbers that are VERY off.

 

[Hui]

Aoballer

Firstly, Welcome to the Chandoo.org Forums

Can you post the data or file?

 

[NARAYANK991]

Hi ,

Try this in the first cell , and copy down.

=SUMPRODUCT((E$2:E$100=E2)*(AV$2:AV$100>AV2)) + COUNTIFS(E$2:E2,E2,AV$2:AV2,AV2)

Narayan