Issue with RANK.EQ and COUNTIFS Formula Not Ranking Correctly in Excel

MK1212

New Member
Joined
Feb 5, 2025
Messages
1
Office Version
  1. 2010
i am using the formula =RANK.EQ(C8, $C$8:$C$428) + COUNTIFS($C$8:$C$428, $C8, $D$8:$D$428, ">" & $D8) + COUNTIFS($C$8:$C$428, $C8, $D$8:$D$428, $D8, $E$8:$E$428, ">" & $E8) in Excel, but it is not ranking the values as expected. It does not seem to rank the data correctly in descending order as I intend. Could anyone help explain why this formula isn't working as intended and how I can fix it?"

PRIZES NUMBERSPOINTS
117264.13
117275
117234.783
117285.87
2336104.39
3555156.89
3555260.42
238395.175
117275.543
239199.889
117246.196
11729.7826
116459.489
116469.343
3555206.52
2336111.84
1172100
116418.613
217249.457
238373.667
116433.942
117224.457
11646.5693
117242.391
11645.8394
121920.339
233619.946
116421.533
116452.92
116463.139
117235.326
2336106.34
121961.017
2336126.81
117250.543
116477.737
2391115.57
116478.102
116452.555
233644.343
233643.76
12198.4746
116429.562
233693.141
239128.648
116442.701
116435.401
3555181.53
2391190.26
116455.109
116456.569
116459.124
121917.797
116489.781
2391123.14
116417.518
116489.416
2391172.55
121932.203
116471.898
116423.358
239177.837
117254.348
3555133.47
117250
3555292.42
238385.661
117223.913
116468.248
355558.751
355571.866
117246.739
239181.494
233670.72
117222.826
233682.867
117227.174
117215.217
116451.46
233671.688
117271.196
12191.6949
116414.599
117238.043
355567.087
233695.613
233679.534
3555123.08
117244.565
233665.071
3555228.59
2336163.41
238349.468
116449.635
116496.715
116433.212
121957.627
3555155.77
3555103.73
100
117225.543
116454.745
2391163.12
3555182.29
116432.482
121934.746
116473.358
2391125.17
117241.304
116443.431
116467.153
3555225.2
2336177.09
3555190.04
117212.5
117236.957
2391131.98
117244.022
121948.305
121912.712
2336128.13
117233.696
2383163.89
11727.6087
117292.391
117215.761
3555131.52
121933.898
121935.593
117267.935
121949.153
117276.087
3555236.14
233677.991
116486.861
116418.978
117277.174
116427.007
2336153.61
233696.553
116463.869
116471.168
117234.239
117276.63
2391108.16
117219.022
2391133.78
121977.119
116476.277
238399.474
117232.065
238399.431
117241.848
117272.826
355598.282
116463.504
121916.102
3555250.81
117260.87
2391167.45
2336149.57
121952.542
238377.156
3555231.05
116445.985
117269.022
117235.87
121958.475
117261.413
2336137.08
2383172.7
116497.81
3555224.4
116426.642
116424.818
117280.435
121998.305
11725.9783
239168.93
117221.196
355565.424
233684.921
116460.584
116416.788
3555297.83
116418.248
116411.679
238377.737
116457.299
2383150.97
11645.4745
116465.328
117259.239
2336136.41
116494.891
238392.02
238389.484
239173.904
11723.8043
11724.8913
117265.761
11729.2391
116446.35
116451.095
117252.174
12193.3898
121960.169
121955.085
121962.712
121930.508
116494.526
116461.314
116481.387
117277.717
117259.783
2383156.56
116488.686
116428.102
121926.271
238345.676
2383138.79
2383110.28
116455.839
116465.693
12196.7797
238376.135
116445.255
116417.153
116464.964
116419.708
116498.905
117263.587
11641.8248
233674.123
3555179.32
2336117.82
116427.372
116491.971
116431.752
2336144.3
233695.565
2336101.42
11646.9343
116483.942
116479.927
116493.431
117210.87
116464.599
11722.1739
11725.4348
116467.883
238371.65
238390.598
116461.679
116448.905
116411.314
116413.504
116435.036
116498.54
116494.161
116448.175
117226.63
116485.036
2336127.14
2336139.24
116456.204
11647.2993
116482.847
3555273.76
116450
116479.562
117233.152
121914.407
2391102.99
121966.949
2383109.26
239148.462
11728.6957
117222.283
233612.873
116447.445
2383192.11
238377.657
238385.773
116499.27
11642.1898
116478.832
239191.203
2383114.64
116486.496
116447.81
121988.983
116481.752
2336110.9
117284.783
116493.796
2383100.27
11724.3478
116474.088
238374.05
116425.182
116438.321
116477.007
116470.803
116453.65
3555108.23
121931.356
2336110.46
2336166.35
11648.0292
2336140.79
116483.212
117214.13
116415.693
116451.825
117266.848
121996.61
116414.234
116441.971
117266.304
117240.217
116491.241
233664.61
116424.453
117220.652
233647.719
116468.978
117228.261
116413.139
11642.9197
11643.2847
11645.1095
116410.949
116460.949
116412.409
217219.565
116458.029
11721.6304
116450.73
116482.117
116473.723
11644.0146
233699.421
116432.847
11649.1241
116422.628
117294.022
116453.285
116437.591
116430.657
116420.438
2383136.27
116477.372
121928.814
116458.394
121923.729
121942.373
117228.804
116416.058
117247.826
2336115.16
233662.948
116425.547
116471.533
116444.891
116469.708
117232.609
117239.674
233661.096
116459.854
116458.759
11723.2609
116485.766
3555276.53
116476.642
121951.695
116472.993
11641.0949
116438.686
116462.044
117213.043
121943.22
3555289.08
116479.197
116464.234
116483.577
116480.657
239199.714
116421.168
121974.576
11727.0652
116462.409
11728.1522
11726.5217
117214.674
11648.7591
116425.912
116440.876
116443.796
116456.934
2383190.52
117268.478
116423.723
116447.08
116435.766
116466.058
12195.0847
233681.577
2391127.76
3555242.49
117263.043
11649.4891
116462.774
116484.672
116470.073
116448.54
11722.7174
233654.292
116436.496
116486.131
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Welcome to the Forum!

You haven't defined "correctly", or shown us the results you want to see, but is this what you're after?

CDEFGHIJK
7PRIZESNUMBERSPOINTSSorted by col F
8117264.1301713555260.420
9117275.0001623555206.520
10117234.7832133555156.890
11117285.870144239199.889
122336104.39095238395.175
133555156.89036238373.667
143555260.420172336111.840
15238395.175582336106.340
16117275.5431592336104.390
17239199.889410233619.946
18117246.1961811217249.457
1911729.7832312121920.339
20116459.48926131172100.000
21116469.3432414117285.870
223555206.520215117275.543
232336111.840716117275.000
241172100.0001317117264.130
25116418.6133018117246.196
26217249.4571119117242.391
27238373.667620117235.326
28116433.9422821117234.783
29117224.4572222117224.457
3011646.569312311729.783
31117242.3911924116469.343
3211645.8393225116463.139
33121920.3391226116459.489
34233619.9461027116452.920
35116421.5332928116433.942
36116452.9202729116421.533
37116463.1392530116418.613
38117235.326203111646.569
392336106.34083211645.839
Sheet1
Cell Formulas
RangeFormula
I8:K39I8=INDEX(C$8:C$39,MATCH($H8,$F$8:$F$39,),)
F8:F39F8=RANK.EQ(C8,$C$8:$C$39)+COUNTIFS($C$8:$C$39,$C8,$D$8:$D$39,">"&$D8)+COUNTIFS($C$8:$C$39,$C8,$D$8:$D$39, $D8, $E$8:$E$39,">"&$E8)
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

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