So the logic should be that the formula would output a no. in a reversed numbered list format in column 1, and for those who are "Dismissed" or "Resigned" in column 3, the formula would skip them and the next numbering would be a follow-up from the previous no. instead.

CodePudding user response：

Formula for you

=ArrayFormula(IF(C2:C="Current",INDEX(SORT({COUNTIFS(INDEX(SORT({C2:C,ROW(C2:C)},2,0),0,1),"Current",ROW(C2:C),"<="&ROW(C2:C)),ROW(C2:C)},1,0),0,1),""))

Function References

• 

  CodePudding user response：

  Alternative: Use Custom Function

  You may also create a custom function using Google Apps Script like the one below:

  function customFunction(range) {
    var out = [];
    var count = 0;
    for (i = 0; i <= range.length-1; i  ) {
      (range[i][1] != "Resigned" && range[i][1] != "Dismissed") ? count   : count;
    }
    for (i = 0; i <= range.length-1; i  ) {
      if ((range[i][1] != "Resigned") && (range[i][1] != "Dismissed")) {
        out.push([count]);
        count--;
      } else {
        out.push([""]);
      }
    }
    return out;
  }
  

  Usage

  You may rename the customFunction name to whatever you want. To use the customFunction, you just need to input the following syntax:

  =customFunction(B3:C9)