# Rank Function Help

#### ashwin28

##### New Member
Hi,

I have a Salesperson worksheet having the following structure:
Column A Column B
Person1 Sales
Person2 Sales
Person3 Sales
Sub Total
Person 4 Sales
Person 5 Sales
Person 6 Sales
Sub Total

and so forth. When using the RANK Function, I want to exclude the subtotal column in the range field else RANK Function will rank that also. How do I go about excluding the subtotal column in RANK functions range field ?

ash

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

##### MrExcel MVP
ashwin28 said:
Hi,

I have a Salesperson worksheet having the following structure:
Column A Column B
Person1 Sales
Person2 Sales
Person3 Sales
Sub Total
Person 4 Sales
Person 5 Sales
Person 6 Sales
Sub Total

and so forth. When using the RANK Function, I want to exclude the subtotal column in the range field else RANK Function will rank that also. How do I go about excluding the subtotal column in RANK functions range field ?

ash

How did you calculate subtotals - by means of a formula or otherwise?

#### ashwin28

##### New Member
I used the SUM function to arrive at SubTotals

#### barry houdini

##### MrExcel MVP
If your subtotals are in column B what text will be in column A for these rows, or is column A blank?

##### MrExcel MVP

ashwin28 said:
I used the SUM function to arrive at SubTotals
Book13
ABCD
1Sales personSalesRank
2Person1763.653
3Person2785.442
4Person3570.015
5Subtotal2119.1
6Person4618.934
7Person5418.486
8Person6931.831
9Subtotal1969.24
10
Sheet1

C2, copied down:

=IF(A2<>"",IF(A2="Subtotal","",RANK(B2,\$B\$2:\$B\$9)-COUNTIF(\$A\$2:\$A\$9,"Subtotal")),"")

#### barry houdini

##### MrExcel MVP

wouldn't this give incorrect results if any of the individual sales figures are higher than any of the subtotals?

assuming the text "subtotal" in column A when a subtotal appears in column B I'd suggest

=IF(AND(A2<>"",A2<>"subtotal"),SUMPRODUCT(--(\$A\$2:\$A\$9<>"subtotal"),--(\$B\$2:\$B\$9>B2))+1,"")

##### MrExcel MVP

barry houdini said:

wouldn't this give incorrect results if any of the individual sales figures are higher than any of the subtotals?

assuming the text "subtotal" in column A when a subtotal appears in column B I'd suggest

=IF(AND(A2<>"",A2<>"subtotal"),SUMPRODUCT(--(\$A\$2:\$A\$9<>"subtotal"),--(\$B\$2:\$B\$9>B2))+1,"")

I failed to test for that possibility. I should have kept it to the method of ranking twice...
Book13
ABCD
1Sales personSalesI-RankF-Rank
2Person1763.6543
3Person2785.4452
4Person3570.0125
5Subtotal2119.1
6Person4618.9334
7Person5418.4816
8Person6931.8361
9Subtotal1969.24
10
Sheet1

C2, copied down:

=IF(A2="subtotal","",RANK(B2,\$B\$2:\$B\$9,1))

D2, copied down:

=IF(N(C2),RANK(C2,\$C\$2:\$C\$9),"")

#### ashwin28

##### New Member
I am sorry guys I could not understand the last post on I rank and F ranks ? Could you please explain ?

Thanks,

Ash

#### joelnichols

##### Active Member
The first column is used to make sure there are no "persons" with a higher sales than one of the subtotals I think. Then the next column ranks the persons after that is filtered out.

##### MrExcel MVP
ashwin28 said:
I am sorry guys I could not understand the last post on I rank and F ranks ? Could you please explain ?

Thanks,

Ash

I-Rank (intermediate ranking) eliminates ranking unwanted values, that is, the subtotals that you create with a SUM formula. F-Rank (final ranking) is the desired ranking, based on I-Rank.

Replies
5
Views
152
Replies
8
Views
188
Replies
8
Views
210
Replies
1
Views
80
Replies
12
Views
624