google-sheetsarray-formulasnumbered-list

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

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.

Solution

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