Hi -
I am trying to rank a set of data based on a separate criteria (brand) from the ranking list (assortment size). I need the resulting data to be able to have duplicates, but not skip any numbers as I am going to use the rank results in a vlookup column reference formula.
Each of the below formulas get me about half way there and I haven't been able to figure out how to combine them.
This formula ranks the data based on the brand criteria, but it skips numbers in the resulting rank.
=(COUNTIFS($A$1:$A$7,A1,$B$1:$B$7,"<"&B1)+1)
This formula ranks correctly and does not skip any numbers, but it doesn't allow me to use the brand list criteria.
=SUMPRODUCT((B1 > B$1:B$7)/COUNTIF(B$1:B$7,B$1:B$7))+1
Can anyone help me combine these 2 formulas?
Data Example:
A B C
Brand Assortment Size Rank
Casio 20
Casio 20
Casio 40
Casio 40
Fossil 20
Fossil 20
Fossil 40
Results in C with the top formula
C
1
1
3
3
1
1
3
Desired results in C
C
1
1
2
2
1
1
2
Can anyone help?
Thanks!
I am trying to rank a set of data based on a separate criteria (brand) from the ranking list (assortment size). I need the resulting data to be able to have duplicates, but not skip any numbers as I am going to use the rank results in a vlookup column reference formula.
Each of the below formulas get me about half way there and I haven't been able to figure out how to combine them.
This formula ranks the data based on the brand criteria, but it skips numbers in the resulting rank.
=(COUNTIFS($A$1:$A$7,A1,$B$1:$B$7,"<"&B1)+1)
This formula ranks correctly and does not skip any numbers, but it doesn't allow me to use the brand list criteria.
=SUMPRODUCT((B1 > B$1:B$7)/COUNTIF(B$1:B$7,B$1:B$7))+1
Can anyone help me combine these 2 formulas?
Data Example:
A B C
Brand Assortment Size Rank
Casio 20
Casio 20
Casio 40
Casio 40
Fossil 20
Fossil 20
Fossil 40
Results in C with the top formula
C
1
1
3
3
1
1
3
Desired results in C
C
1
1
2
2
1
1
2
Can anyone help?
Thanks!
