google-sheets - 如何将 =ARRAYFORMULA() 应用于 Google 表格中的反向编号列表？

Question

<表类="s-表"><头>没有。姓名就业状况<正文>(此处插入公式样本名称就业类型5约翰全职玛丽辞职4 jack 兼职3蒂姆契约(Contract)简解雇2约翰全职1拉里兼职

所以逻辑上应该是公式会输出一个 no。在第 1 列中以反向编号列表格式，对于第 3 列中“解雇”或“辞职”的人，该公式将跳过它们，下一个编号将是前一个编号的后续编号。相反。

Answer 1

像这样尝试 COUNTIF()-

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