Excel Unique Rank on Multiple Criteria w/o Duplicates

Y

Yotes1608

New Member
Joined
Mar 12, 2018
Messages
2
I have a problem where I need to rank locations based on two separate criteria without duplicates. I have exhausted my Excel friends and researched options but cannot seem to get a formula to do what I need. I've even tried to figure out a helper column with no luck. Please help if you can:

My locations are uniquely ranked based on their frequency lowest to highest and where the frequency is tied, the location with the higher hours would receive the better rank. Additionally, if the location does not have any hours, they would be ignored. Below is an example.

[TABLE="width: 376"]
<tbody>[TR]
[TD]Hours[/TD]
[TD]Freq.[/TD]
[TD][/TD]
[TD]Expected Rank[/TD]
[/TR]
[TR]
[TD]30350.81[/TD]
[TD]13.17921993[/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]28175.1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]21421[/TD]
[TD]9.336632277[/TD]
[TD][/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]23454.37[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]42535.96[/TD]
[TD]18.80761596[/TD]
[TD][/TD]
[TD]16[/TD]
[/TR]
[TR]
[TD]25288.65[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]51895.99[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]12054.25[/TD]
[TD]16.59165854[/TD]
[TD][/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]47288.82[/TD]
[TD]4.229329469[/TD]
[TD][/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]48367.66[/TD]
[TD]4.134994333[/TD]
[TD][/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]42694.1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]44717.64[/TD]
[TD]13.41752382[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]25154.08[/TD]
[TD]7.950996419[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]30600.44[/TD]
[TD]6.535853733[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]12871.45[/TD]
[TD]31.07652984[/TD]
[TD][/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]62441.3[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]19000.91[/TD]
[TD]10.52581166[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

Thank you in advance for any time and assistance.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IF(<font color="Blue">A2=0,"",COUNTIFS(<font color="Red">B$2:B$19,"<"&B2,$A$2:$A$19,">0"</font>)+COUNTIFS(<font color="Red">B$2:B$19,B2,$A$2:$A$19,">0",A$2:A$19,">"&A2</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />


Excel 2013/2016
ABC
1HoursFreq.
230350.813.179213
328175.104
4214219.3366311
523454.406
64253618.807616
725288.705
85189602
912054.316.591715
1047288.84.229338
1148367.74.134997
1242694.103
1344717.613.417514
1425154.17.95110
1530600.46.535859
1612871.531.076517
1700
1862441.301
1919000.910.525812
Sheet1
 
Upvote 0
[TABLE="width: 1120"]
<colgroup><col><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD]Hours[/TD]
[TD]Freq.[/TD]
[TD] [/TD]
[TD]Expected Rank[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30350.81[/TD]
[TD="align: right"]13.17921993[/TD]
[TD] [/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28175.1[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21421[/TD]
[TD="align: right"]9.336632277[/TD]
[TD] [/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23454.37[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42535.96[/TD]
[TD="align: right"]18.80761596[/TD]
[TD] [/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25288.65[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]51895.99[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12054.25[/TD]
[TD="align: right"]16.59165854[/TD]
[TD] [/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]47288.82[/TD]
[TD="align: right"]4.229329469[/TD]
[TD] [/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]48367.66[/TD]
[TD="align: right"]4.134994333[/TD]
[TD] [/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42694.1[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44717.64[/TD]
[TD="align: right"]13.41752382[/TD]
[TD] [/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25154.08[/TD]
[TD="align: right"]7.950996419[/TD]
[TD] [/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30600.44[/TD]
[TD="align: right"]6.535853733[/TD]
[TD] [/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12871.45[/TD]
[TD="align: right"]31.07652984[/TD]
[TD] [/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62441.3[/TD]
[TD="align: right"]0[/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19000.91[/TD]
[TD="align: right"]10.52581166[/TD]
[TD] [/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Hours[/TD]
[TD]Freq.[/TD]
[TD]helper1[/TD]
[TD]helper2[/TD]
[TD]helper3[/TD]
[TD]helper4[/TD]
[TD]helper5[/TD]
[TD]helper6[/TD]
[TD]helper7[/TD]
[TD]helper8[/TD]
[TD]helper9[/TD]
[TD]helper10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30350.81[/TD]
[TD="align: right"]13.17921993[/TD]
[TD="align: right"]30350.81[/TD]
[TD][/TD]
[TD="align: right"]62441.3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]13.17922[/TD]
[TD="align: right"]4.134994[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]13.17922[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28175.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]28175.1[/TD]
[TD="align: right"]28175.1[/TD]
[TD="align: right"]51895.99[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="align: right"]4.229329[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21421[/TD]
[TD="align: right"]9.336632277[/TD]
[TD="align: right"]21421[/TD]
[TD][/TD]
[TD="align: right"]42694.1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9.336632[/TD]
[TD="align: right"]6.535854[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9.336632[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23454.37[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]23454.37[/TD]
[TD="align: right"]23454.37[/TD]
[TD="align: right"]28175.1[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]7.950996[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42535.96[/TD]
[TD="align: right"]18.80761596[/TD]
[TD="align: right"]42535.96[/TD]
[TD][/TD]
[TD="align: right"]25288.65[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]18.80762[/TD]
[TD="align: right"]9.336632[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]18.80762[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]16[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25288.65[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25288.65[/TD]
[TD="align: right"]25288.65[/TD]
[TD="align: right"]23454.37[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]10.52581[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]51895.99[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]51895.99[/TD]
[TD="align: right"]51895.99[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.17922[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12054.25[/TD]
[TD="align: right"]16.59165854[/TD]
[TD="align: right"]12054.25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]16.59166[/TD]
[TD="align: right"]13.41752[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16.59166[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]47288.82[/TD]
[TD="align: right"]4.229329469[/TD]
[TD="align: right"]47288.82[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.229329[/TD]
[TD="align: right"]16.59166[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4.229329[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]48367.66[/TD]
[TD="align: right"]4.134994333[/TD]
[TD="align: right"]48367.66[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]4.134994[/TD]
[TD="align: right"]18.80762[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]4.134994[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]42694.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]42694.1[/TD]
[TD="align: right"]42694.1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31.07653[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]44717.64[/TD]
[TD="align: right"]13.41752382[/TD]
[TD="align: right"]44717.64[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.41752[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]13.41752[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25154.08[/TD]
[TD="align: right"]7.950996419[/TD]
[TD="align: right"]25154.08[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.950996[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7.950996[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30600.44[/TD]
[TD="align: right"]6.535853733[/TD]
[TD="align: right"]30600.44[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.535854[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]6.535854[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12871.45[/TD]
[TD="align: right"]31.07652984[/TD]
[TD="align: right"]12871.45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31.07653[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]31.07653[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]62441.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]62441.3[/TD]
[TD="align: right"]62441.3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19000.91[/TD]
[TD="align: right"]10.52581166[/TD]
[TD="align: right"]19000.91[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10.52581[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]10.52581[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]would you accept 10 helper columns ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you so much! That works perfect. I am truly grateful for the help and support.


VBA Geek said:
Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IF(A2=0,"",COUNTIFS(B$2:B$19,"<"&B2,$A$2:$A$19,">0")+COUNTIFS(B$2:B$19,B2,$A$2:$A$19,">0",A$2:A$19,">"&A2)+1)[/TD]
[/TR]
</tbody>[/TABLE]



Excel 2013/2016
ABC

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Hours[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Freq.[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30350.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13.1792[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]28175.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]21421[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9.33663[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]11[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]23454.4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]42536[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]18.8076[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25288.7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]51896[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12054.3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]16.5917[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]47288.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4.22933[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]8[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]48367.7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]4.13499[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]42694.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]44717.6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]13.4175[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]14[/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25154.1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]7.951[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30600.4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]6.53585[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]9[/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12871.5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]31.0765[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]17[/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]62441.3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]19000.9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10.5258[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]12[/TD]

</tbody>
Sheet1​
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top