I see the variety of much more complicated questions being asked here and am a little embarrassed at how much simpler mine sounds. I'm sorry about that. But I can't seem to solve this on my own.
I want to rank a grouping within a larger table of values based on their sale counts. The following conditions must be met or are involved in the rankings:
...where applying it would give me the following rankings for a filtered group in a filtered year:
What I actually got was:
All of it seems correct except for the rankings for Echo and Kilo, who should be =3rd. I can see that they have the same number of sales as India, but they do not have any award, so the highest they can be ranked is 3. The other rankings for everyone else appear to be correct, and ties are acceptable.
I want to rank a grouping within a larger table of values based on their sale counts. The following conditions must be met or are involved in the rankings:
- I need an ordering of each region for each year, within a table of many regions covering many years; salespeople are ordered by year and then by name, not by group.
- The #1 and #2 rankings are awarded to salespeople based on other criteria.
- The #3 through #n rankings are based on sales count.
Excel Formula:
=IF([@[Year End Award]]="Y",1,
IF([@[6-Month Award]]="Y",2,
IF(AND([@[Year End Award]]="",[@[6-Month Award]]=""),COUNTIFS($A$2:$A$137,A4,$I$2:$I$137,I4,$F$2:$F$137,">"&F4)+1)))...where applying it would give me the following rankings for a filtered group in a filtered year:
| Year | Name | Sales | Year End Award | 6-Month Award | RANKING |
|---|---|---|---|---|---|
| 2022 | Alfa | 3 | 8 | ||
| 2022 | Bravo | 2 | 9 | ||
| 2022 | Charlie | 2 | 9 | ||
| 2022 | Delta | 1 | 11 | ||
| 2022 | Echo | 7 | 3 | ||
| 2022 | Foxtrot | 6 | 5 | ||
| 2022 | Golf | 1 | 11 | ||
| 2022 | Hotel | 6 | 5 | ||
| 2022 | India | 8 | Y | 2 | |
| 2022 | Juliett | 7 | Y | 1 | |
| 2022 | Kilo | 7 | 3 | ||
| 2022 | Lima | 4 | 7 |
What I actually got was:
| Year | Name | Sales | Year End Award | 6-Month Award | RANKING |
|---|---|---|---|---|---|
| 2022 | Alfa | 3 | 8 | ||
| 2022 | Bravo | 2 | 9 | ||
| 2022 | Charlie | 2 | 9 | ||
| 2022 | Delta | 1 | 11 | ||
| 2022 | Echo | 7 | 2 | ||
| 2022 | Foxtrot | 6 | 5 | ||
| 2022 | Golf | 1 | 11 | ||
| 2022 | Hotel | 6 | 5 | ||
| 2022 | India | 8 | Y | 2 | |
| 2022 | Juliett | 7 | Y | 1 | |
| 2022 | Kilo | 7 | 2 | ||
| 2022 | Lima | 4 | 7 |
All of it seems correct except for the rankings for Echo and Kilo, who should be =3rd. I can see that they have the same number of sales as India, but they do not have any award, so the highest they can be ranked is 3. The other rankings for everyone else appear to be correct, and ties are acceptable.
