Results 1 to 2 of 2

Thread: NEED HELP: Countif tie breaker with multiple tie breaker criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default NEED HELP: Countif tie breaker with multiple tie breaker criteria

    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 Survey Exclusion category for ranking? Jan Jan Var. Jan Rank Jan Var. for rank only Jan # Responses Jan Weight Feb Feb Var. Feb Rank Feb Var. for rank only Feb # Responses Feb Weight
    1 Yes
    2 53.6 -0.3 61 -0.3 15501 98.5% 53 -0.6 38 -0.6 16575 98.7%
    3 61.5 -1.4 54 -1.4 15465 98.3% 61 -0.5 40 -0.5 16540 98.5%
    4 47.1 -4.8 27 -4.8 13402 85.2% 46.9 -0.2 44 -0.2 14176 84.4%
    5 Yes
    6 76 -3 38 -3 1310 8.3% 76.2 0.2 48 0.2 1288 7.7%
    7 69.4 -4.3 32 -4.3 1325 8.4% 70.4 1 56 1 1300 7.7%
    8 65.3 -7 11 -7 1305 8.3% 70.8 5.5 65 5.5 1279 7.6%
    9 61.9 -7.7 9 -7.7 1836 11.7% 64 2.1 61 2.1 1849 11.0%
    10 60.3 -5.8 18 -5.8 1831 11.6% 60.6 0.3 50 0.3 1844 11.0%
    11 53.4 -9.5 6 -9.5 1810 11.5% 54.5 1.1 57 1.1 1814 10.8%
    12 Yes
    13 67.1 -0.8 59 -0.8 15401 97.9% 67.6 0.5 54 0.5 16495 98.2%
    14 70.7 -1.5 51 -1.5 15559 98.9% 70.9 0.2 49 0.2 16605 98.9%
    15 72.1 -3.5 34 -3.5 4459 28.3% 71.3 -0.8 36 -0.8 4590 27.3%
    16 69 -0.6 60 -0.6 4381 27.8% 68.3 -0.7 37 -0.7 4517 26.9%
    17 77.1 -2.8 40 -2.8 4122 26.2% 74.7 -2.4 13 -2.4 4256 25.3%
    18 70.9 -3.6 33 -3.6 3657 23.2% 69.2 -1.7 23 -1.7 3692 22.0%
    19 70.9 -6.4 14 -6.4 4485 28.5% 68.7 -2.2 15 -2.2 4610 27.5%
    20 62 -5 25 -5 835 5.3% 65.5 3.5 63 3.5 838 5.0%
    21 Yes
    22 53.1 -2.6 44 -2.6 15321 97.4% 52 -1.1 31 -1.1 16334 97.3%
    23 66.3 -3.2 36 -3.2 15442 98.1% 67.2 0.9 55 0.9 16479 98.1%
    24 49.4 -6.2 16 -6.2 4488 28.5% 47.2 -2.2 17 -2.2 4613 27.5%
    25 66.9 -3.4 35 -3.4 4436 28.2% 66.8 -0.1 45 -0.1 4560 27.2%
    26 58.4 -5.2 23 -5.2 4387 27.9% 55.7 -2.7 11 -2.7 4523 26.9%
    27 65.5 -4.6 28 -4.6 4393 27.9% 64.4 -1.1 32 -1.1 4520 26.9%
    28 53.2 -2.7 43 -2.7 4363 27.7% 51.9 -1.3 28 -1.3 4489 26.7%
    29 56.3 -4.4 31 -4.4 4490 28.5% 54.1 -2.2 18 -2.2 4622 27.5%
    30 41 -6.1 17 -6.1 12860 81.7% 40.6 -0.4 41 -0.4 13953 83.1%
    31 42.4 -5.5 20 -5.5 12367 78.6% 42.8 0.4 53 0.4 13226 78.8%
    32 Yes
    33 47.3 -7.9 8 -7.9 1959 12.4% 46.9 -0.4 42 -0.4 1933 11.5%
    34 53.7 -6.7 12 -6.7 1963 12.5% 53.6 -0.1 46 -0.1 1922 11.4%
    35 69.8 -5 25 -5 1984 12.6% 68.4 -1.4 27 -1.4 1978 11.8%
    36 74.4 -3.1 37 -3.1 1886 12.0% 74.4 0 47 0 1887 11.2%
    37 53.8 -4.5 29 -4.5 1953 12.4% 53.4 -0.4 43 -0.4 1933 11.5%
    38 Yes
    39 Yes 57.5 -8.8 724 4.6% 58.5 1 751 4.5%
    40 Yes 62.4 -1 726 4.6% 61.5 -0.9 749 4.5%
    41 Yes 76 -0.5 725 4.6% 71.4 -4.6 751 4.5%
    42 Yes 79.6 -1.1 721 4.6% 78.4 -1.2 742 4.4%
    43 Yes 61.8 -4 723 4.6% 61.3 -0.5 747 4.4%
    44 Yes
    45 Yes 53.9 -6.7 1444 9.2% 53.5 -0.4 1396 8.3%
    46 Yes 56.8 -3.8 1448 9.2% 53.7 -3.1 1396 8.3%
    47 Yes 69.8 -3.2 1445 9.2% 67 -2.8 1396 8.3%
    48 Yes 77.8 -1.5 1440 9.1% 74.7 -3.1 1388 8.3%
    49 Yes 55.8 -3.9 1439 9.1% 56.7 0.9 1383 8.2%
    50 Yes
    51 Yes 52.7 -7.1 347 2.2% 37.6 -15.1 340 2.0%
    52 Yes 54.5 -2.7 347 2.2% 41.6 -12.9 339 2.0%
    53 Yes 68 -3 347 2.2% 64.3 -3.7 339 2.0%
    54 Yes 78.2 -3.3 344 2.2% 71.3 -6.9 335 2.0%
    55 Yes 53 -6 345 2.2% 41.9 -11.1 339 2.0%
    56 Yes
    57 Yes 60.2 -3.6 161 1.0% 44.8 -15.4 154 0.9%
    58 Yes 57.5 -4.9 160 1.0% 46.8 -10.7 154 0.9%
    59 Yes 70.2 -8.2 161 1.0% 64.3 -5.9 154 0.9%
    60 Yes 76.1 -6.6 159 1.0% 74.7 -1.4 154 0.9%
    61 Yes 56 -1.7 159 1.0% 41.6 -14.4 154 0.9%
    62 Yes
    63 Yes 47 4.1 N/A 0.0% 44.9 -2.1 N/A 0.0%
    64 Yes 63.6 6.5 N/A 0.0% 57.1 -6.5 N/A 0.0%
    65 Yes 76.9 12.2 N/A 0.0% 66.2 -10.7 N/A 0.0%
    66 Yes 71.2 9.4 N/A 0.0% 74 2.8 N/A 0.0%
    67 Yes 42.4 8.1 N/A 0.0% 37.7 -4.7 N/A 0.0%
    68 Yes
    69 Yes N/A N/A 0 0.0% N/A N/A 0 0.0%
    70 Yes N/A N/A 0 0.0% N/A N/A 0 0.0%
    71 Yes N/A N/A 0 0.0% N/A N/A 0 0.0%
    72 Yes N/A N/A 0 0.0% N/A N/A 0 0.0%
    73 Yes N/A N/A 0 0.0% N/A N/A 0 0.0%
    74 Yes
    75 64.8 -3 38 -3 918 5.8% 59.7 -5.1 4 -5.1 885 5.3%
    76 66.5 -2.4 47 -2.4 920 5.8% 61.1 -5.4 2 -5.4 887 5.3%
    77 73.5 -1 58 -1 920 5.8% 69 -4.5 5 -4.5 889 5.3%
    78 Yes
    79 49.6 -9.6 5 -9.6 956 6.1% 46.4 -3.2 9 -3.2 933 5.6%
    80 63.3 -6.3 15 -6.3 948 6.0% 61.5 -1.8 22 -1.8 931 5.5%
    81 77.2 -1.3 56 -1.3 925 5.9% 75.5 -1.7 24 -1.7 901 5.4%
    82 72.3 -1.5 51 -1.5 954 6.1% 68.7 -3.6 6 -3.6 932 5.5%
    83 49.4 -6.4 13 -6.4 952 6.0% 46.3 -3.1 10 -3.1 922 5.5%
    84 32.8 -12.4 1 -12.4 952 6.0% 27.2 -5.6 1 -5.6 937 5.6%
    85 Yes
    86 52.3 -10.6 3 -10.6 1000 6.4% 48.9 -3.4 7 -3.4 1082 6.4%
    87 63.8 -8.6 7 -8.6 867 5.5% 65 1.2 58 1.2 926 5.5%
    88 52 -9.9 4 -9.9 832 5.3% 54.1 2.1 62 2.1 878 5.2%
    89 61.9 -7.7 9 -7.7 818 5.2% 63.8 1.9 60 1.9 865 5.2%
    90 54.7 -11.3 2 -11.3 643 4.1% 49.5 -5.2 3 -5.2 705 4.2%
    91 Yes
    92 69.3 -4.5 29 -4.5 498 3.2% 67.7 -1.6 25 -1.6 507 3.0%
    93 76.5 -5.4 21 -5.4 497 3.2% 80.1 3.6 64 3.6 507 3.0%
    94 78 -1.8 49 -1.8 492 3.1% 79.4 1.4 59 1.4 499 3.0%
    95 75.4 -1.4 55 -1.4 480 3.0% 73.1 -2.3 14 -2.3 480 2.9%
    96 82.5 -1.3 56 -1.3 491 3.1% 79.2 -3.3 8 -3.3 505 3.0%
    97 Yes
    98 67.5 -5.6 19 -5.6 1765 11.2% 65.9 -1.6 26 -1.6 1658 9.9%
    99 81.2 -2.5 45 -2.5 1763 11.2% 79.1 -2.1 19 -2.1 1657 9.9%
    100 78.5 -2.7 41 -2.7 1758 11.2% 76.5 -2 20 -2 1653 9.8%
    101 68.5 -1.5 51 -1.5 1758 11.2% 66.3 -2.2 16 -2.2 1649 9.8%
    102 24.3 -5.2 24 -5.2 1709 10.9% 23.4 -0.9 35 -0.9 1612 9.6%
    103 Yes
    104 Yes 63.2 -4.4 318 2.0% 57.6 -5.6 344 2.0%
    105 Yes 75.2 0.3 315 2.0% 70.6 -4.6 344 2.0%
    106 Yes 54.4 -0.6 316 2.0% 45.3 -9.1 342 2.0%
    107 Yes 28.7 -2.7 296 1.9% 21.1 -7.6 332 2.0%
    108 Yes 61.8 -6.1 301 1.9% 63.9 2.1 321 1.9%
    109 Yes
    110 Yes 65.5 -1.2 58 0.4% 46.5 -19 71 0.4%
    111 Yes 75.9 0.9 58 0.4% 71.4 -4.5 70 0.4%
    112 Yes 55.4 -8.7 56 0.4% 35.2 -20.2 71 0.4%
    113 Yes 82.6 27.6 23 0.1% 75 -7.6 40 0.2%
    114 Yes
    115 Yes 71.4 71.4 35 0.2% 53.7 -17.7 54 0.3%
    116 Yes 73.3 7.8 135 0.9% 73.4 0.1 143 0.9%
    117 Yes 67.2 14.6 134 0.9% 59.3 -7.9 145 0.9%
    118 Yes 59.5 14 79 0.5% 69.1 9.6 81 0.5%
    119 Yes 63.9 97.2 36 0.2% 30.2 -33.7 53 0.3%
    120 Yes
    121 Yes 100 100 2 0.0% 83.3 -16.7 6 0.0%
    122 Yes 100 100 2 0.0% 83.3 -16.7 6 0.0%
    123 Yes 100 100 2 0.0% 50 -50 6 0.0%
    124 Yes 50 50 2 0.0% -16.7 -66.7 6 0.0%
    125 Yes
    126 Yes 76.9 12.2 13 0.1% 60 -16.9 45 0.3%
    127 Yes 76.9 -5.5 13 0.1% 82.2 5.3 45 0.3%
    128 Yes 84.6 8.1 13 0.1% 57.8 -26.8 45 0.3%
    129 Yes
    130 Yes 71.4 -8.6 7 0.0% 100 28.6 5 0.0%
    131 Yes 71.4 -28.6 7 0.0% 100 28.6 5 0.0%
    132 Yes 66.7 -13.3 6 0.0% -40 -106.7 5 0.0%
    133 Yes
    134 72.3 2 62 2 15281 97.1% 71.2 -1.1 33 -1.1 16293 97.0%
    135 79.4 -1.9 48 -1.9 4122 26.2% 78.3 -1.1 30 -1.1 4256 25.3%
    136 72.3 2 62 2 15281 97.1% 71.2 -1.1 34 -1.1 16293 97.0%
    137 74.7 -2.7 41 -2.7 3132 19.9% 72.3 -2.4 12 -2.4 3157 18.8%
    138 78.9 -1.6 50 -1.6 4180 26.6% 79.3 0.4 52 0.4 4253 25.3%
    139 75.5 -2.5 45 -2.5 3831 24.3% 74.3 -1.2 29 -1.2 3959 23.6%
    140 62.8 -5.3 22 -5.3 3806 24.2% 63.1 0.3 51 0.3 3869 23.0%
    141 Yes
    142 36.9 7 65 7 2863 18.2% 36.3 -0.6 39 -0.6 2857 17.0%
    143 37.5 4.2 64 4.2 2604 16.5% 35.6 -1.9 21 -1.9 2593 15.4%

  2. #2
    Board Regular DRSteele's Avatar
    Join Date
    Mar 2015
    Location
    Calgary
    Posts
    1,987
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    1 Thread(s)

    Default Re: NEED HELP: Countif tie breaker with multiple tie breaker criteria

    Following these threads and their links might help.

    https://www.mrexcel.com/forum/excel-questions/1021796-ranking-multiple-tiebreakers.html?highlight=tiebreakers
    https://www.mrexcel.com/forum/excel-questions/1029946-rank-4-tie-break-criteria.html?highlight=tiebreakers
    Windows10, Excel 365 Insider
    If you don't declare what version of Excel, we will assume
    Windows10, Excel 365.
    Formulas here are always in
    Green background
    You can get the HTML Maker: https://www.mrexcel.com/forum/excel-...ins-links.html.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •