# rank by criteria

#### shannonsmith

##### New Member
I essentially need a formula that says for every market (there are 20), rank all of the products within that market by \$ Vol (highest to lowest).

I've tried to filter on the markets and then rank the product, but the file becomes very slow because there are 20 Markets, each containing roughly 30,000 products. Any suggestions would be greatly appreciated.

 MKT PROD \$ Vol Total Retail SUTTER HOME 1.5 LITERS \$ 96,201,869.00 Total Retail SUTTER HOME 187 MILLITERS \$ 57,751,044.00 Total Retail SUTTER HOME 750 MILLITERS \$ 58,107,054.00 Total Retail SUTTER HOME 3 LITERS \$ - Total Retail SUTTER HOME \$ 212,059,967.00 Total Retail BAREFOOT 1.5 LITERS \$ 153,541,287.00 Total Retail BAREFOOT 187 MILLITERS \$ 17,358,324.00 Total Retail BAREFOOT 750 MILLITERS \$ 188,904,750.00 Total Retail BAREFOOT \$ 359,804,361.00 Total Retail BRAND FAMILY \$ 571,864,328.00 TARGET TOTAL CTA SUTTER HOME 1.5 LITERS \$ 2,177,572.00 TARGET TOTAL CTA SUTTER HOME 187 MILLITERS \$ 2,388,573.00 TARGET TOTAL CTA SUTTER HOME 750 MILLITERS \$ 1,296,227.00 TARGET TOTAL CTA SUTTER HOME 3 LITERS \$ - TARGET TOTAL CTA SUTTER HOME \$ 5,862,372.00 TARGET TOTAL CTA BAREFOOT 1.5 LITERS \$ 4,749,233.00 TARGET TOTAL CTA BAREFOOT 187 MILLITERS \$ 28.00 TARGET TOTAL CTA BAREFOOT 750 MILLITERS \$ 5,933,944.00 TARGET TOTAL CTA BAREFOOT \$ 10,683,205.00

<tbody>
</tbody>

### 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)

#### p45cal

##### Well-known Member
Formula in D2 is copied down:
Excel Workbook
ABCD
1MKTPROD\$ Vol
2Total RetailSUTTER HOME 1.5 LITERS96,201,869.005
3Total RetailSUTTER HOME 187 MILLITERS57,751,044.007
4Total RetailSUTTER HOME 750 MILLITERS58,107,054.006
5Total RetailSUTTER HOME 3 LITERS10
6Total RetailSUTTER HOME1,296,227.009
7Total RetailBAREFOOT 1.5 LITERS153,541,287.004
8Total RetailBAREFOOT 187 MILLITERS17,358,324.008
9Total RetailBAREFOOT 750 MILLITERS188,904,750.003
10Total RetailBAREFOOT359,804,361.002
11Total RetailBRAND FAMILY571,864,328.001
12TARGET TOTAL CTASUTTER HOME 1.5 LITERS2,177,572.006
13TARGET TOTAL CTASUTTER HOME 187 MILLITERS2,388,573.005
14TARGET TOTAL CTASUTTER HOME 750 MILLITERS1,296,227.007
15TARGET TOTAL CTASUTTER HOME 3 LITERS9
16TARGET TOTAL CTASUTTER HOME5,862,372.003
17TARGET TOTAL CTABAREFOOT 1.5 LITERS4,749,233.004
18TARGET TOTAL CTABAREFOOT 187 MILLITERS288
19TARGET TOTAL CTABAREFOOT 750 MILLITERS5,933,944.002
20TARGET TOTAL CTABAREFOOT10,683,205.001
Sheet

#### shannonsmith

##### New Member
p45cal- I can't explain my gratitude for helping me out. This formula will save me hours of work. Thank you so much. If you have time to respond, could you explain the formula? For the life of me I can't figure out how the second half of the formula works, which I assume is the rank part. If you don't have time, no big deal. I'm just glad it works!!

1,195,903
Messages
6,012,206
Members
441,681
Latest member
AkosiJessica

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