Formula to Rank - Ignore column if not empty

[jaime1182]

Hi all. I hope you are all well. I am having some issues with a rank formula.

I need to do two ranking formulas. One is to get the overall rank (minus scores where the resit column isn't blank) as well as the rank within the course (again, minus the scores where the resit column isn't blank)

ID	Score	Resit	Course	Overall	Course Rank
AB001	8.587		A	6 of 7	3 of 3
AC035	12.50		A	2 of 7	1 of 3
BA913	10.20		A	4 of 7	2 of 3
OB312	3.15	Y	A
AB001	9.15		B	5 of 7	3 of 4
AC035	13.67		B	1 of 7	1 of 4
BA913	11.63		B	3 of 7	2 of 4
OB312	5.15		B	7 of 7	4 of 4

Before the resit column, it was somewhat straightforward.

I could use RANK(B1,B:B,0).

With the ranks for each individual subject, I had to make other sheets to rank those, then copy that back to this sheet.
I'm sure there's a faster way of doing this but am not entirely sure how.

But now with the complication of the resit column, I don't know how to ignore scores without actually deleting them.

Is anyone able to help me think this through? How should I go about doing this?

 

[jasonb75]

These should do it

=IF(C2<>"","",(COUNTIFS(B:B,">"&B2,C:C,"")+1)&" of "&COUNTIFS(B:B,B2,C:C,""))

=IF(C2<>"","",(COUNTIFS(B:B,">"&B2,C:C,"",D:D,D2)+1)&" of "&COUNTIFS(B:B,B2,C:C,"",D:D,D2))

 

[jaime1182]

Worked an absolute treat!! Thanks so much!!!!