Numbering Based on Condition(s)-CodePudding

I'm trying to create auto numbering for Agents that are currently present and has numbers including zeroes 0 in 3rd or 4th column(zero meaning they don't get any stats but they are present)

Agents who has TEXT Value in the 3rd or 4th column are those who are not present (Ex: A = Absent, SL = Sick Leave, VL = Vacation Leave). Meaning, they should not be counted, therefore their value on 1st column should be blank, and therefore this should not stop the auto numbering for the rest of the agents below and should continue the count in sequence.

Can anyone help create formula that would fill the numbers on the 1st column automatically for those agents that are present and has value including 0 on column 3 or 4 (stats 1 or stats 2)?

To give more idea, I'm trying to show the current total number of agents who are currently present in this situation and will count their stats, and exclude all other agents who are not present and should not be counted.

Thank you!

CodePudding user response：

You can try the following in cell A1:

=LET(B, B2:B12, C, C2:C12, f, 1*ISNUMBER(B*C),seq, SEQUENCE(ROWS(B)),
  MAP(seq, LAMBDA(s, IF(INDEX(f,s)=0, "",SUM(FILTER(f, (seq<=s),0))))))

Here is the output:

A non-array version, expanding down the formula would be:

=IF(ISNUMBER(B2*C2), SUM(1*ISNUMBER(B$2:B2*C$2:C2)),"")

For the array version, it counts only if both columns Stat1 and Stat2 are numeric. The name f, has a value of 1 if the condition is TRUE, otherwise is 0. The MAP does the count if the index position of the f array is not zero, otherwise returns an empty string.

CodePudding user response：

Sequence Two Numeric Columns

Single Cell

In cell A3, a basic not spilling formula would be...
```
=IF(AND(ISNUMBER(C3),ISNUMBER(D3)),MAX(A$2:A2) 1,"") 
```
... with the condition of a string in cell A2.
Without any conditions, you could try an improved version, similar to one of David Leal's suggestions:
```
=IF(AND(ISNUMBER(C3),ISNUMBER(D3)),
    SUM(ISNUMBER(C$3:C3)*ISNUMBER(D$3:D3)),"")
```

Spill

In cell A3 you could use the following:
```
=LET(Data1,C3:C13,Data2,D3:D13,
    Data,ISNUMBER(Data1)*ISNUMBER(Data2),
IFERROR(SCAN(0,Data,LAMBDA(a,b,a b))/Data,""))
```
- Line1: the inputs ('constants'), the same-sized single-column ranges
- Line2: the zeros and ones, where the ones present the data of interest
- Line3: the formula to replace the ones with the sequence and the zeros (errors due to division by zero) with an empty string

Converted to a LAMBDA, it could look like the following:

=LAMBDA(Data1,Data2,LET(
    Data,ISNUMBER(Data1)*ISNUMBER(Data2),
IFERROR(SCAN(0,Data,LAMBDA(a,b,a b))/Data,"")))(C3:C13,D3:D13)

Since it's such a long formula, you could create your own Lambda function by using this part...
```
=LAMBDA(Data1,Data2,LET(
    Data,ISNUMBER(Data1)*ISNUMBER(Data2),
IFERROR(SCAN(0,Data,LAMBDA(a,b,a b))/Data,"")))
```
... to define a name, e.g. SeqNumeric, when in the same cell, you could use it simply with...
```
=SeqNumeric(C3:C13,D3:D13)
```
... instead.
Now you can use the function like any other Excel function anywhere in the workbook.

The Path

F3  =ISNUMBER(C3:C13)*ISNUMBER(D3:D13) - multiply: zeros-no, ones-yes
G3  =SCAN(0,F3#,LAMBDA(a,b,a b)) - use the 'LAMBDA' helper function 'SCAN'
H3  =G3#/F3# - divide the 'scan' result by the zeros and ones
I3  =IFERROR(H3#,"") - convert the '#DIV/0!' errors to empty strings

The translation of the SCAN part could be something like the following:

Set the initial result a to 0.
Create a new array of the size of the initial array in F3#.
Loop through each element of the initial array, write its value to b, and replace a with the sum of a b.
Write (the accumulated) a to the current element of the new array and repeat for the remaining elements of either array.
Return the new array.

Combine all of it in a LET.

J3  =LET(Data1,C3:C13,Data2,D3:D13,
        Data,ISNUMBER(Data1)*ISNUMBER(Data2),
    IFERROR(SCAN(0,Data,LAMBDA(a,b,a b))/Data,""))

Convert to LAMBDA.

K3  =LAMBDA(Data1,Data2,LET(
        Data,ISNUMBER(Data1)*ISNUMBER(Data2),
    IFERROR(SCAN(0,Data,LAMBDA(a,b,a b))/Data,"")))(C3:C13,D3:D13)

Copy the first part of the LAMBDA (note how it results in a #CALC! error since no parameters are supplied)...

L3  =LAMBDA(Data1,Data2,LET(
        Data,ISNUMBER(Data1)*ISNUMBER(Data2),
    IFERROR(SCAN(0,Data,LAMBDA(a,b,a b))/Data,"")))

... and select Formulas -> Name Manager -> New to create your own function and finally use it with the following:

A3   =SeqNumeric(C3:C13,D3:D13)

CodePudding user response：

I think I got it.

This is the formula that I made

=IF(COUNTIFS(D2:BE2,"*",$D$1:$BE$1,TODAY())>0,"",MAX(A1:A$4) 1)

Countif criteria 1 = if the cell contains a letter and is counted > 0 then it will return blank, otherwise it will start the count using max function. The countif criteria 2 will will return the correct value according to the date today since the excel sheet has several data daily.

Thank you all so much!