CODEWITHSUNDEEP
google-sheetsarray-formulasnumbered-list
qazamy
qazamy

Reputation: 45

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.

Upvotes: 2

Views: 445

Answers (4)

qazamy
qazamy

Reputation: 45

=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

Upvotes: 0

Theza
Theza

Reputation: 613

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

Upvotes: 3

Harun24hr
Harun24hr

Reputation: 36965

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

Upvotes: 3

PatrickdC
PatrickdC

Reputation: 2431

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)

Upvotes: 1

Related Questions