I did a little reading on the subject, but cannot quite find how to do it if my criteria range is more than one column. The formula I have in mind is something like this:
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Suppose my data contains a list of names on a sheet called "Salary" in column B. This is not the main sheet, but a secondary sheet.
On the main sheet, called "Worksheet", I have data my macro puts out.
What I would like to do is rank each row based on Column O for each name on the list. I assume I would have 23 helper columns ( A helper column for each name), unless someone can think of a better solution. The only criteria is that the row must contain the name it is ranking between columns G:K, otherwise it does not rank it at all. Thanks for any assistance!
=COUNTIFS(criteria_range,criteria,values,">"&value)+1
Suppose my data contains a list of names on a sheet called "Salary" in column B. This is not the main sheet, but a secondary sheet.
| correlationattempt2.xlsm | |||
|---|---|---|---|
| B | |||
| 1 | Name | ||
| 2 | Alec Pierce | ||
| 3 | Ashton Dulin | ||
| 4 | Austin Ekeler | ||
| 5 | Cameron Dicker | ||
| 6 | Chase McLaughlin | ||
| 7 | DeAndre Carter | ||
| 8 | Deon Jackson | ||
| 9 | Donald Parham Jr. | ||
| 10 | Gerald Everett | ||
| 11 | Indianapolis Colts | ||
| 12 | Jelani Woods | ||
| 13 | Jordan Wilkins | ||
| 14 | Joshua Kelley | ||
| 15 | Joshua Palmer | ||
| 16 | Justin Herbert | ||
| 17 | Keenan Allen | ||
| 18 | Los Angeles Chargers | ||
| 19 | Michael Pittman Jr. | ||
| 20 | Mike Williams | ||
| 21 | Nick Foles | ||
| 22 | Parris Campbell | ||
| 23 | Zack Moss | ||
Salary | |||
On the main sheet, called "Worksheet", I have data my macro puts out.
| correlationattempt2.xlsm | |||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
| 1 | QB | FLEX | FLEX | FLEX | FLEX | ComboID | Salary | Projection | Probability | Target | Depth Total | TARGET PT 9+ | Ownership L|U | Correlation | Value | Stack | Stack POS | Players | Filter | Player1 | Player2 | ||
| 2 | Justin Herbert | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | Gerald Everett | 30499 | 56000 | 68.325 | 101.285 | 68.74 | 5 | 3 | 0.05% | 0.29 | 1.220089 | LAC | QB,K,TE | 3 | 0 | ||||
| 3 | Justin Herbert | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | Parris Campbell | 30846 | 55500 | 64.295 | 102.425 | 66.91 | 6 | 3 | 0.41% | 0.34 | 1.158468 | LAC | QB,K,TE | 3 | 0 | ||||
| 4 | Justin Herbert | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | Alec Pierce | 30847 | 54500 | 63.915 | 98.915 | 66.4 | 7 | 3 | 0.45% | 0.34 | 1.172752 | LAC | QB,K,TE | 3 | 0 | ||||
| 5 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Cameron Dicker | Gerald Everett | 35119 | 59500 | 73.9 | 98.14 | 88.6 | 5 | 3 | 0.06% | 0.35 | 1.242016 | LAC | RB,QB,K,TE | 4 | 0 | ||||
| 6 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Cameron Dicker | Gerald Everett | 7399 | 59500 | 75.475 | 96.525 | 88.6 | 5 | 3 | 0.06% | 0.35 | 1.268487 | LAC | QB,RB,K,TE | 4 | 0 | ||||
| 7 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Cameron Dicker | Parris Campbell | 35121 | 60000 | 72.24 | 124.7 | 84.86 | 6 | 4 | 0.04% | 0.04 | 1.204 | LAC | RB,QB,K | 3 | 0 | ||||
| 8 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Cameron Dicker | Alec Pierce | 35122 | 59000 | 71.86 | 121.19 | 84.35 | 7 | 4 | 0.05% | 0.04 | 1.217966 | LAC | RB,QB,K | 3 | 0 | ||||
| 9 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Cameron Dicker | Parris Campbell | 7401 | 60000 | 73.815 | 123.085 | 84.86 | 6 | 4 | 0.04% | 0.04 | 1.23025 | LAC | QB,RB,K | 3 | 0 | ||||
| 10 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Cameron Dicker | Alec Pierce | 7402 | 59000 | 73.435 | 119.575 | 84.35 | 7 | 4 | 0.05% | 0.04 | 1.244661 | LAC | QB,RB,K | 3 | 0 | ||||
| 11 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Gerald Everett | Parris Campbell | 35136 | 59500 | 70.61 | 124.7 | 88.13 | 6 | 4 | 0.59% | 0.27 | 1.186722 | LAC | RB,QB,TE | 3 | 0 | ||||
| 12 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Gerald Everett | Alec Pierce | 35137 | 58500 | 70.23 | 121.19 | 87.62 | 7 | 4 | 0.65% | 0.27 | 1.200512 | LAC | RB,QB,TE | 3 | 0 | ||||
| 13 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Gerald Everett | Parris Campbell | 7416 | 59500 | 72.185 | 123.085 | 88.13 | 6 | 4 | 0.59% | 0.27 | 1.213193 | LAC | QB,RB,TE | 3 | 0 | ||||
| 14 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Gerald Everett | Alec Pierce | 7417 | 58500 | 71.805 | 119.575 | 87.62 | 7 | 4 | 0.65% | 0.27 | 1.227435 | LAC | QB,RB,TE | 3 | 0 | ||||
| 15 | Austin Ekeler | Justin Herbert | Cameron Dicker | Gerald Everett | Parris Campbell | 35466 | 59000 | 69.87 | 99.28 | 86.77 | 6 | 3 | 0.51% | 0.5 | 1.184237 | LAC | RB,QB,K,TE | 4 | 0 | ||||
| 16 | Austin Ekeler | Justin Herbert | Cameron Dicker | Gerald Everett | Alec Pierce | 35467 | 58000 | 69.49 | 95.77 | 86.26 | 7 | 3 | 0.56% | 0.5 | 1.198103 | LAC | RB,QB,K,TE | 4 | 0 | ||||
| 17 | Justin Herbert | Austin Ekeler | Cameron Dicker | Gerald Everett | Parris Campbell | 7746 | 59000 | 71.445 | 97.665 | 86.77 | 6 | 3 | 0.51% | 0.5 | 1.210932 | LAC | QB,RB,K,TE | 4 | 0 | ||||
| 18 | Justin Herbert | Austin Ekeler | Cameron Dicker | Gerald Everett | Alec Pierce | 7747 | 58000 | 71.065 | 94.155 | 86.26 | 7 | 3 | 0.56% | 0.5 | 1.225258 | LAC | QB,RB,K,TE | 4 | 0 | ||||
| 19 | Austin Ekeler | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | Gerald Everett | 62839 | 56500 | 63.6 | 106.13 | 81.17 | 5 | 3 | 0.02% | -0.05 | 1.125663 | LAC | RB,K,TE | 3 | 0 | ||||
| 20 | Austin Ekeler | Justin Herbert | Cameron Dicker | Parris Campbell | Alec Pierce | 35497 | 58500 | 67.83 | 122.33 | 82.52 | 8 | 4 | 0.41% | 0.19 | 1.159487 | LAC | RB,QB,K | 3 | 0 | ||||
| 21 | Justin Herbert | Austin Ekeler | Cameron Dicker | Parris Campbell | Alec Pierce | 7777 | 58500 | 69.405 | 120.715 | 82.52 | 8 | 4 | 0.41% | 0.19 | 1.18641 | LAC | QB,RB,K | 3 | 0 | ||||
| 22 | Austin Ekeler | Justin Herbert | Gerald Everett | Parris Campbell | Alec Pierce | 35827 | 58000 | 66.2 | 122.33 | 85.79 | 8 | 4 | 5.36% | 0.39 | 1.141379 | LAC | RB,QB,TE | 3 | 0 | ||||
| 23 | Justin Herbert | Austin Ekeler | Gerald Everett | Parris Campbell | Alec Pierce | 8107 | 58000 | 67.775 | 120.715 | 85.79 | 8 | 4 | 5.36% | 0.39 | 1.168534 | LAC | QB,RB,TE | 3 | 0 | ||||
| 24 | Austin Ekeler | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | Parris Campbell | 63186 | 56000 | 59.57 | 107.27 | 79.34 | 6 | 3 | 0.19% | 0.08 | 1.06375 | LAC | RB,K,TE | 3 | 0 | ||||
| 25 | Austin Ekeler | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | Alec Pierce | 63187 | 55000 | 59.19 | 103.76 | 78.83 | 7 | 3 | 0.21% | 0.08 | 1.076181 | LAC | RB,K,TE | 3 | 0 | ||||
| 26 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Cameron Dicker | Joshua Kelley | 35123 | 58000 | 71.06 | 98.14 | 74.7 | 6 | 3 | 0.06% | 0.04 | 1.225172 | LAC | RB,QB,K,RB | 4 | 0 | ||||
| 27 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Cameron Dicker | Joshua Kelley | 7403 | 58000 | 72.635 | 96.525 | 74.7 | 6 | 3 | 0.06% | 0.04 | 1.252327 | LAC | QB,RB,K,RB | 4 | 0 | ||||
| 28 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Gerald Everett | Joshua Kelley | 35138 | 57500 | 69.43 | 98.14 | 77.97 | 6 | 3 | 0.76% | 0.27 | 1.207478 | LAC | RB,QB,TE,RB | 4 | 0 | ||||
| 29 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Gerald Everett | Joshua Kelley | 7418 | 57500 | 71.005 | 96.525 | 77.97 | 6 | 3 | 0.76% | 0.27 | 1.234869 | LAC | QB,RB,TE,RB | 4 | 0 | ||||
| 30 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Parris Campbell | Joshua Kelley | 35168 | 58000 | 67.77 | 124.7 | 74.23 | 7 | 4 | 0.55% | -0.04 | 1.168448 | LAC | RB,QB,RB | 3 | 0 | ||||
| 31 | Austin Ekeler | Justin Herbert | Chase McLaughlin | Alec Pierce | Joshua Kelley | 35183 | 57000 | 67.39 | 121.19 | 73.72 | 8 | 4 | 0.61% | -0.04 | 1.18228 | LAC | RB,QB,RB | 3 | 0 | ||||
| 32 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Parris Campbell | Joshua Kelley | 7448 | 58000 | 69.345 | 123.085 | 74.23 | 7 | 4 | 0.55% | -0.04 | 1.195603 | LAC | QB,RB,RB | 3 | 0 | ||||
| 33 | Justin Herbert | Austin Ekeler | Chase McLaughlin | Alec Pierce | Joshua Kelley | 7463 | 57000 | 68.965 | 119.575 | 73.72 | 8 | 4 | 0.61% | -0.04 | 1.209912 | LAC | QB,RB,RB | 3 | 0 | ||||
Worksheet | |||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| Y2:Y33 | Y2 | =IF($AA$2="",COUNTIF(G2:K2,$Z$2),COUNTIF(G2:K2,$Z$2)*COUNTIF(G2:K2,$AA$2)) |
What I would like to do is rank each row based on Column O for each name on the list. I assume I would have 23 helper columns ( A helper column for each name), unless someone can think of a better solution. The only criteria is that the row must contain the name it is ranking between columns G:K, otherwise it does not rank it at all. Thanks for any assistance!
