# Rank within groups, and put text values at the end

#### mbk0523

##### New Member
Hi -- Imagine the below table is a summary sheet. I want to rank the companies in Column C within each of their groups in Column B, based on the data in Column D. I know a sumproduct formula to do that. But the complicating factor now is what's in D4... "N/M" which is text pulled over from a raw data sheet on a different tab. My sumproduct formula is ranking Company M first within Group 1, but I want any "N/M" or "N/A" to be last.

What formula can I write in Column A to do this?

Thanks!

 COL A COL B COL C COL D ROW 1 ??? Group 1 Company J 35.2 ROW 2 Group 1 Company K 55.8 ROW 3 Group 1 Company L 58.3 ROW 4 Group 1 Company M N/M (this is text, looked up from another sheet) ROW 5 Group 2 Company X 17.0 ROW 6 Group 2 Company Y 13.4 ROW 7 Group 2 Company Z 20.2

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Marcelo Branco

##### MrExcel MVP
Maybe...

Pasta1
ABCD
13Group 1Company J35,2
22Group 1Company K55,8
31Group 1Company L58,3
44Group 1Company MN/M
52Group 2Company X17
63Group 2Company Y13,4
71Group 2Company Z20,2
Plan1
Cell Formulas
RangeFormula
A1:A7A1=COUNTIFS(B\$1:B\$100,B1,D\$1:D\$100,">"&D1)+1+IF(ISNUMBER(D1),0,SUMPRODUCT(--(B\$1:B\$100=B1),--ISNUMBER(D\$1:D\$100)))

Hope this helps

M.

Replies
0
Views
196
Replies
1
Views
125
Replies
1
Views
80
Replies
7
Views
161
Replies
27
Views
308

1,141,073
Messages
5,704,140
Members
421,328
Latest member
mippy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back