# Ranking by Category w/o Skipping Rank

1. ## 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.

2. ## Re: Ranking by Category w/o Skipping Rank

Hi aoballer and welcome to the forum,

I think you are looking for this type of formula:

=RANK(B2,\$B\$2:\$B\$21,0)+COUNTIF(\$B\$2:B2,B2)-1

Shown in:
https://www.extendoffice.com/documen...e-rank.html#a1
See attachment for example:
Rank without dups.xlsx

3. ## Re: Ranking by Category w/o Skipping Rank

What are you trying to do with this part?
``Please Login or Register  to view this content.``

4. ## Re: Ranking by Category w/o Skipping Rank

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.

5. ## Re: Ranking by Category w/o Skipping Rank

sanram...

Do you ever sleep?

6. ## Re: Ranking by Category w/o Skipping Rank

Originally Posted by Tony Valko
sanram...

Do you ever sleep?
Sleeping kills time.

7. ## Re: Ranking by Category w/o Skipping Rank

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.
Formula:
`Please Login or Register  to view this content.`

8. ## Re: Ranking by Category w/o Skipping Rank

=1+SUMPRODUCT((A1=A\$1:A\$9)*(B1>B\$1:B\$9))
That formula will skip ranks. The OP wants consecutive ranks with no skips. Like this...

1...21
1...21
1...21
1...87

The OP wants...

1...21...1
1...21...1
1...21...1
1...87...2

1...21...1
1...21...1
1...21...1
1...87...4

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1