How to apply =ARRAYFORMULA() to a reversed numbered list in Google Sheets?

Question

No.	Name	Employment status
(insert formula here	Sample Name	Type of Employment
5	John	Full-time
	Mary	Resigned
4	Jack	Part-time
3	Tim	Contract
	Jane	Dismissed
2	John	Full-time
1	Larry	Part-time

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.

Answer 1

=ARRAYFORMULA(IF(MMULT(N(C2:C8={"Resigned","Dismissed"}),{1;1}),"",MMULT(N(ROW(C2:C8)<=TRANSPOSE(ROW(C2:C8))),1-ISNUMBER(MATCH(C2:C8,{"Resigned","Dismissed"},0)))))

• answered by @Jos Woolley in this thread

Answer 2

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),""))

---

Edit: Include more than one label

=ArrayFormula(IF((C2:C="Full-time")+(C2:C="Part-time")+(C2:C="Contract"),COUNTIFS((C2:C="Full-time")+(C2:C="Part-time")+(C2:C="Contract"),1,ROW(C2:C),">="&ROW(C2:C)),))

or

=ArrayFormula(IF((C2:C="Full-time")+(C2:C="Part-time")+(C2:C="Contract"),COUNTIFS((C2:C="Full-time")+(C2:C="Part-time")+(C2:C="Contract"),">0",ROW(C2:C),">="&ROW(C2:C)),))

or

=ArrayFormula(IF(ISNUMBER(MATCH(C2:C,{"Full-time","Part-time","Contract"},0)),COUNTIFS(MATCH(C2:C,{"Full-time","Part-time","Contract"},0),">0",ROW(C2:C),">="&ROW(C2:C)),))

or

=ArrayFormula(IF(ISNUMBER(SEARCH(C2:C,"Full-time"&"Part-time"&"Contract"))*(C2:C<>""),COUNTIFS(SEARCH(C2:C,"Full-time"&"Part-time"&"Contract")*(C2:C<>""),">0",ROW(C2:C),">="&ROW(C2:C)),))

Function References

• INDEX
• SORT
• COUNTIFS
• ROW
• ISNUMBER
• SEARCH
• MATCH

Answer 3

Try COUNTIF() like-

=Arrayformula(IF(C2:C<>"Current",,COUNTIFS(C2:C, C2:C, ROW(C2:C), ">="&ROW(C2:C))))

Edit: After OP's comment and updated data try below formula-

=Arrayformula(IF(C2:C="",,IF(RegexMatch(C2:C,"Resigned|Dismissed"),,COUNTIFS(C2:C,"*",ROW(C2:C), ">="&ROW(C2:C))-(COUNTIFS(C2:C,"Resigned",ROW(C2:C), ">="&ROW(C2:C))+COUNTIFS(C2:C,"Dismissed",ROW(C2:C), ">="&ROW(C2:C))))))

Answer 4

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)