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

Question

The formula has to be input on cell A2. The logic should be that the formula would result in the following cell (from cell A3 downwards) outputting a no. in a reversed numbered list format in column 1. And for those who are "Resigned" or "Dismissed" in column 3, the formula would skip them and the next numbering would be a follow-up from the previous no. instead.

We're using in-house software that's similar to Google Sheets and Microsoft Excel so certain functions/formulas like REGEX and custom functions are not supported.

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	Jonathan	Full-time
1	Larry	Part-time

This post is a repost from this ask as the solutions weren't applicable to this issue, unfortunately. So I'm looking for alternative solutions from those answers.

Answer 1

can't be simpler:

=ARRAYFORMULA(IF((C2:C="contract")+(C2:C="part-time")+(C2:C="full-time"), 
 COUNTIFS(SUBSTITUTE(C2:C, "Contract", "time"), "*time", ROW(C2:C), ">="&ROW(C2:C)), ))

Answer 2

Alternative:

=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)))))

Note that the oft-seen technique of replacing the upper cell reference in the range passed with a reference to the entire column (for example, C2:C) would here not be advisable. If such flexibility is required, suggest you create a dynamic Named Range.

Answer 3

Paste this formula in A2

= {"Count" ; ArrayFormula(IFNA(IF(B3:B="",,VLOOKUP(B3:B, FILTER({ B3:C, SEQUENCE(ROWS(B3:B),1,COUNTA(B3:B),-1) }, C3:C<>"Resigned",C3:C<>"Dismissed"),3,0)),""))}