Ranking by Category w/o Skipping Rank

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.

What are you trying to do with this part?

Please Login or Register  to view this content.

Originally Posted by Tony Valko

sanram...

Do you ever sleep?

Sleeping kills time.

Originally Posted by Tony Valko

Is this what you had in mind?

Data Range

	A	B	C
1	Cat1	40	1
2	Cat1	50	2
3	Cat1	50	2
4	Cat2	30	1
5	Cat2	40	2
6	Cat2	70	3
7	cat3	20	1
8	Cat3	100	2
9	Cat3	100	2
10	------	------	------

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

=SUM(IF(FREQUENCY(IF(A$1:A$9=A1,IF(B$1:B$9<B1,B$1:B$9)),B$1:B$9),1))+1

** 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.

I have the data sorted just to make it easier to see what results you'll get.

How about this ?

Formula:

Please Login or Register  to view this content.

thanks for your reply

Thank you very much bro