NEED HELP: Countif tie breaker with multiple tie breaker criteria

hockeyguy11

New Member
Joined
Jun 18, 2019
Messages
1
I need a formula that will help me rank the 'Jan Var. for rank only' values, ignoring blanks in between rows, in ascending order. So the largest negative will get rank 1, etc. Then if the values in that column are the same, I want to break the tie by looking at which row has the larger number of responses and award the higher rank to the higher number of responses, then if those values are still the same, award the higher rank to the lower number in the Jan (third) column, then if the values are still the same, award the higher rank to the lower number in the 'order of survey' column. I left in the 'Jan Rank' column which is using the RANK.EQ function but this function allows ties and so I am looking for help creating the COUNTIF/COUNTIFS tie breaker formula to add to it that will account for all the things I've mentioned. Thanks in advance!


Order of SurveyExclusion category for ranking?JanJan Var.Jan RankJan Var. for rank onlyJan # ResponsesJan WeightFebFeb Var.Feb RankFeb Var. for rank onlyFeb # ResponsesFeb Weight
1Yes
253.6-0.361-0.31550198.5%53-0.638-0.61657598.7%
361.5-1.454-1.41546598.3%61-0.540-0.51654098.5%
447.1-4.827-4.81340285.2%46.9-0.244-0.21417684.4%
5Yes
676-338-313108.3%76.20.2480.212887.7%
769.4-4.332-4.313258.4%70.4156113007.7%
865.3-711-713058.3%70.85.5655.512797.6%
961.9-7.79-7.7183611.7%642.1612.1184911.0%
1060.3-5.818-5.8183111.6%60.60.3500.3184411.0%
1153.4-9.56-9.5181011.5%54.51.1571.1181410.8%
12Yes
1367.1-0.859-0.81540197.9%67.60.5540.51649598.2%
1470.7-1.551-1.51555998.9%70.90.2490.21660598.9%
1572.1-3.534-3.5445928.3%71.3-0.836-0.8459027.3%
1669-0.660-0.6438127.8%68.3-0.737-0.7451726.9%
1777.1-2.840-2.8412226.2%74.7-2.413-2.4425625.3%
1870.9-3.633-3.6365723.2%69.2-1.723-1.7369222.0%
1970.9-6.414-6.4448528.5%68.7-2.215-2.2461027.5%
2062-525-58355.3%65.53.5633.58385.0%
21Yes
2253.1-2.644-2.61532197.4%52-1.131-1.11633497.3%
2366.3-3.236-3.21544298.1%67.20.9550.91647998.1%
2449.4-6.216-6.2448828.5%47.2-2.217-2.2461327.5%
2566.9-3.435-3.4443628.2%66.8-0.145-0.1456027.2%
2658.4-5.223-5.2438727.9%55.7-2.711-2.7452326.9%
2765.5-4.628-4.6439327.9%64.4-1.132-1.1452026.9%
2853.2-2.743-2.7436327.7%51.9-1.328-1.3448926.7%
2956.3-4.431-4.4449028.5%54.1-2.218-2.2462227.5%
3041-6.117-6.11286081.7%40.6-0.441-0.41395383.1%
3142.4-5.520-5.51236778.6%42.80.4530.41322678.8%
32Yes
3347.3-7.98-7.9195912.4%46.9-0.442-0.4193311.5%
3453.7-6.712-6.7196312.5%53.6-0.146-0.1192211.4%
3569.8-525-5198412.6%68.4-1.427-1.4197811.8%
3674.4-3.137-3.1188612.0%74.40470188711.2%
3753.8-4.529-4.5195312.4%53.4-0.443-0.4193311.5%
38Yes
39Yes57.5-8.87244.6%58.517514.5%
40Yes62.4-17264.6%61.5-0.97494.5%
41Yes76-0.57254.6%71.4-4.67514.5%
42Yes79.6-1.17214.6%78.4-1.27424.4%
43Yes61.8-47234.6%61.3-0.57474.4%
44Yes
45Yes53.9-6.714449.2%53.5-0.413968.3%
46Yes56.8-3.814489.2%53.7-3.113968.3%
47Yes69.8-3.214459.2%67-2.813968.3%
48Yes77.8-1.514409.1%74.7-3.113888.3%
49Yes55.8-3.914399.1%56.70.913838.2%
50Yes
51Yes52.7-7.13472.2%37.6-15.13402.0%
52Yes54.5-2.73472.2%41.6-12.93392.0%
53Yes68-33472.2%64.3-3.73392.0%
54Yes78.2-3.33442.2%71.3-6.93352.0%
55Yes53-63452.2%41.9-11.13392.0%
56Yes
57Yes60.2-3.61611.0%44.8-15.41540.9%
58Yes57.5-4.91601.0%46.8-10.71540.9%
59Yes70.2-8.21611.0%64.3-5.91540.9%
60Yes76.1-6.61591.0%74.7-1.41540.9%
61Yes56-1.71591.0%41.6-14.41540.9%
62Yes
63Yes474.1N/A0.0%44.9-2.1N/A0.0%
64Yes63.66.5N/A0.0%57.1-6.5N/A0.0%
65Yes76.912.2N/A0.0%66.2-10.7N/A0.0%
66Yes71.29.4N/A0.0%742.8N/A0.0%
67Yes42.48.1N/A0.0%37.7-4.7N/A0.0%
68Yes
69YesN/AN/A00.0%N/AN/A00.0%
70YesN/AN/A00.0%N/AN/A00.0%
71YesN/AN/A00.0%N/AN/A00.0%
72YesN/AN/A00.0%N/AN/A00.0%
73YesN/AN/A00.0%N/AN/A00.0%
74Yes
7564.8-338-39185.8%59.7-5.14-5.18855.3%
7666.5-2.447-2.49205.8%61.1-5.42-5.48875.3%
7773.5-158-19205.8%69-4.55-4.58895.3%
78Yes
7949.6-9.65-9.69566.1%46.4-3.29-3.29335.6%
8063.3-6.315-6.39486.0%61.5-1.822-1.89315.5%
8177.2-1.356-1.39255.9%75.5-1.724-1.79015.4%
8272.3-1.551-1.59546.1%68.7-3.66-3.69325.5%
8349.4-6.413-6.49526.0%46.3-3.110-3.19225.5%
8432.8-12.41-12.49526.0%27.2-5.61-5.69375.6%
85Yes
8652.3-10.63-10.610006.4%48.9-3.47-3.410826.4%
8763.8-8.67-8.68675.5%651.2581.29265.5%
8852-9.94-9.98325.3%54.12.1622.18785.2%
8961.9-7.79-7.78185.2%63.81.9601.98655.2%
9054.7-11.32-11.36434.1%49.5-5.23-5.27054.2%
91Yes
9269.3-4.529-4.54983.2%67.7-1.625-1.65073.0%
9376.5-5.421-5.44973.2%80.13.6643.65073.0%
9478-1.849-1.84923.1%79.41.4591.44993.0%
9575.4-1.455-1.44803.0%73.1-2.314-2.34802.9%
9682.5-1.356-1.34913.1%79.2-3.38-3.35053.0%
97Yes
9867.5-5.619-5.6176511.2%65.9-1.626-1.616589.9%
9981.2-2.545-2.5176311.2%79.1-2.119-2.116579.9%
10078.5-2.741-2.7175811.2%76.5-220-216539.8%
10168.5-1.551-1.5175811.2%66.3-2.216-2.216499.8%
10224.3-5.224-5.2170910.9%23.4-0.935-0.916129.6%
103Yes
104Yes63.2-4.43182.0%57.6-5.63442.0%
105Yes75.20.33152.0%70.6-4.63442.0%
106Yes54.4-0.63162.0%45.3-9.13422.0%
107Yes28.7-2.72961.9%21.1-7.63322.0%
108Yes61.8-6.13011.9%63.92.13211.9%
109Yes
110Yes65.5-1.2580.4%46.5-19710.4%
111Yes75.90.9580.4%71.4-4.5700.4%
112Yes55.4-8.7560.4%35.2-20.2710.4%
113Yes82.627.6230.1%75-7.6400.2%
114Yes
115Yes71.471.4350.2%53.7-17.7540.3%
116Yes73.37.81350.9%73.40.11430.9%
117Yes67.214.61340.9%59.3-7.91450.9%
118Yes59.514790.5%69.19.6810.5%
119Yes63.997.2360.2%30.2-33.7530.3%
120Yes
121Yes10010020.0%83.3-16.760.0%
122Yes10010020.0%83.3-16.760.0%
123Yes10010020.0%50-5060.0%
124Yes505020.0%-16.7-66.760.0%
125Yes
126Yes76.912.2130.1%60-16.9450.3%
127Yes76.9-5.5130.1%82.25.3450.3%
128Yes84.68.1130.1%57.8-26.8450.3%
129Yes
130Yes71.4-8.670.0%10028.650.0%
131Yes71.4-28.670.0%10028.650.0%
132Yes66.7-13.360.0%-40-106.750.0%
133Yes
13472.326221528197.1%71.2-1.133-1.11629397.0%
13579.4-1.948-1.9412226.2%78.3-1.130-1.1425625.3%
13672.326221528197.1%71.2-1.134-1.11629397.0%
13774.7-2.741-2.7313219.9%72.3-2.412-2.4315718.8%
13878.9-1.650-1.6418026.6%79.30.4520.4425325.3%
13975.5-2.545-2.5383124.3%74.3-1.229-1.2395923.6%
14062.8-5.322-5.3380624.2%63.10.3510.3386923.0%
141Yes
14236.97657286318.2%36.3-0.639-0.6285717.0%
14337.54.2644.2260416.5%35.6-1.921-1.9259315.4%

<tbody>
</tbody>
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Following these threads and their links might help.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.mrexcel.com/forum/excel-questions/1021796-ranking-multiple-tiebreakers.html?highlight=tiebreakers
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.mrexcel.com/forum/excel-questions/1029946-rank-4-tie-break-criteria.html?highlight=tiebreakers[/FONT]<strike>
</strike>
[/FONT]
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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