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

user_16575813 0
Viewed 138
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.

numbered-list array-formulas google-sheets
4 Answers

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

enter image description here

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

enter image description here

Function References

user_128560850

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

enter image description here

user_55147470

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

=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
user_165758130
Related