Ranking with multiple criteria and eliminating duplicates

[NSAdventurer]

Good-evening, I have been searching for awhile now and I have not found a solution, so please forgive me if I have somehow missed it. I have a set of data as follows:

Col A = Group
Col B = # of Item A in the Group
Col C = # of Item B in the Group
Col D = total # in the Group
Col E = % of Item A in the Group

I would like to rank the Group by the number or Item A and then break ties with percentage of A in the Group.

If Group 3 and 4 came back with 3 of Item A, but respectively had a percentage content of 50% and 75% I would like Group 4 to rank higher.

I have tried several sum and averaging functions to no avail. Any suggestions? Very much appreciated!

[:) Sixthsense :)]

Please attach a sample workbook with expected output for better understanding

[NSAdventurer]

Sorry, I have not attached before. I hope this works.

sample.xlsx

[:) Sixthsense :)]

Please show the expected output also

[micope21]

Hi

Welcome to the forum!

Please see the file.

[NSAdventurer]

Thank you very much micope21, for the welcome and the answer!

Could you please help me understand the formula?

=RANK(B3,B$2:B$50)+SUMPRODUCT(--(B$2:B$50=B3),--(E$2:E$50>E3))

I understand the RANK, but I am unfamiliar with the "--" that appear in the SUMPRODUCT portion of the formula. What do they represent or mean?

[Lemice]

Hello,

For lots of IF statement, (this = that, there > here, etc), it will often return boolean values (True / False).
If you add "--" before the bracket, it will return "1" or "0" respectively for "True" or "False", thus making it easier for calculating (counting, for example).
In the formula above, SUMPRODUCT acts as a counting formula, returning a list of "0" and "1" for each value that fits the criteria in it, then add them.
Hope this help.

Regards

[micope21]

The answer what Lemice said above.