Rank within groups, and put text values at the end

mbk0523

New Member
Joined
Aug 29, 2012
Messages
20
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 ACOL BCOL CCOL D
ROW 1???Group 1Company J35.2
ROW 2Group 1Company K55.8
ROW 3Group 1Company L58.3
ROW 4Group 1Company MN/M (this is text, looked up from another sheet)
ROW 5Group 2Company X17.0
ROW 6Group 2Company Y13.4
ROW 7Group 2Company Z20.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
Joined
Aug 23, 2010
Messages
16,852
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top