Search code examples
excelexcel-formulaformula

Numbering Based on Condition(s)

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.

enter image description here

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!

Solution

  • 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.