EXCEL rank.eq function: rank numbers in a row in relation to other numbers of the same assigned category

Question

MY Spreadsheet was made using Excel 2016. I have a table with different numbers with assigned categories:

Size	Category
11	solid
12	liquid
11	liquid
7	liquid
9	solid

I want to return the rank of the numbers, but only rank them based on other numbers in the same category like this:

Size	Category	rank
11	solid	1
12	liquid	1
11	liquid	2
7	liquid	3
9	solid	2

My current solution that accomplishes this requires me to add two new rows for each category:

Size	Category	rank =IF(liquid rank<>"",liquid rank,IF(solid rank<>"",solid rank))	liquid size =IF(category="liquid",size,"")	liquid rank =IF(liquid size="","",RANK.EQ(liquid size,liquid size))	solid size =IF(category="solid",size,"")	solid rank =IF(solid size="","",RANK.EQ(solid size,solid size))
11	solid	1			11	1
12	liquid	1	12	1
11	liquid	2	11	2
7	liquid	3	7	3
9	solid	2			9	2

Is there a way to have all the calculations happening in the additional rows be run inside the RANK row and thus get rid of them? Or is there a more elegant solution as a whole?

Answer 1

You could maybe try:

Formula in C2:

=COUNTIF(B2:B6,B2:B6)-COUNTIFS(A2:A6,"<"&A2:A6,B2:B6,B2:B6)

I suppose the Excel-2016 variant to drag down would be:

=COUNTIF(B$2:B$6,B2)-COUNTIFS(A$2:A$6,"<"&A2,B$2:B$6,B2)

Answer 2

Try COUNTIFS().

 =COUNTIFS($B$2:$B2,$B2)