I'm searching a solution that can be accomplished use the basic function provided by Excel/Google sheet (without touching vba).

The problem is this, I'm building some sort of record of a leaderboard.

• Given the column A recording who is the leader X in that week.
• I want some naive formula in column B able to tell me this the n-th term of X as leader
• Column C will have formula counting this is the m-th week in X's current term.

This is a MWE in Google Sheet:

Explanation for B10 - the function performs the calculation in two parts:

1. $A$2:A10=A10 is converted to {A,A,A,B,B,C,A,A,A}=A and returns an boolean array {T,T,T,F,F,F,T,T,T}
2. $A$1:A9<>A10 is converted to {Given,A,A,A,B,B,C,A,A}<>A and returns an boolean array {T,F,F,F,T,T,T,F,F}
3. both arrays are combined with the AND operator which returns array {T,F,F,F,F,F,T,F,F}
4. from which COUNTIFS counts 2