Rank within groups, and put text values at the end

[mbk0523]

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

 

[Marcelo Branco]

Maybe...

Pasta1
	A	B	C	D
1	3	Group 1	Company J	35,2
2	2	Group 1	Company K	55,8
3	1	Group 1	Company L	58,3
4	4	Group 1	Company M	N/M
5	2	Group 2	Company X	17
6	3	Group 2	Company Y	13,4
7	1	Group 2	Company Z	20,2
Plan1

Cell Formulas
Range	Formula
A1:A7	A1	=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.