Rank values with COUNTIF, plus ties, 2nd largest and top name

rank countif large

Rank a list with COUNTIF and handle ties, then pull the Nth largest value and the top name.

The problem

You want a rank for each row, ties handled, and the name of the top item.

Sample data

Actuals in C2:C5: Rent 1000, Food 450, Fun 100, Misc 100 (names in A).

Formulas

Rank:           =COUNTIF(C$2:C$5,">"&C4)+1   -> 3 (Fun)
Tie-break rank: =COUNTIF(C$2:C$5,">"&C5)+COUNTIF(C$2:C5,""&C5)   -> 4 (Misc)
2nd largest:    =LARGE(C2:C5,2)   -> 450
Top name:       =INDEX(A2:A5,MATCH(LARGE(C2:C5,1),C2:C5,0))   -> Rent

Excel and Sheets also have a built-in rank function; we tested the COUNTIF way.

Excel vs Sheets: all four formulas gave the same results in the checker's Excel and Google Sheets modes, with no warnings.

Tested on sample data by the Cellwise checker (log CW-PL-20261018-45). AI-made: the text, voice and visuals are generated by AI.

Checked before publishing: a check.