Counting instances of a request that meets certain conditions but it's not counting sequentially?

Options

Hi, I'm trying to count the number of times a request appears that matches certain conditions. I want the first instance it appears to be 1, and the second instance to be 2 and so forth. Right now, this is what I'm getting when I filter for the requests that meet the conditions I'm looking for. I don't know why it jumps to 17. My formula is below.

=IFERROR(IF(AND(CONTAINS("1st ESCALATION", Notes@row), [Send Missing Information Email to Plants]@row = 1, [1st Escalation date]@row = TODAY()), COUNTIFS(Notes:Notes, "1st ESCALATION", [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, @cell <= UI@row), IF(AND(CONTAINS("2nd ESCALATION", Notes@row), [Send Missing Information Email to Plants]@row = 1, [2nd Escalation Date]@row = TODAY()), COUNTIFS(Notes:Notes, "2nd ESCALATION", [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, @cell <= UI@row), IF(AND(CONTAINS("FINAL ESCALATION", Notes@row), [Send Missing Information Email to Plants]@row = 1, [4th escalation date]@row = TODAY()), COUNTIFS(Notes:Notes, "FINAL ESCALATION", [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, @cell <= UI@row)))), "")

Thank you!

Tags:

Best Answer

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/20/24 Answer ✓
    Options

    @acondie

    =IF(NOT(OR(CONTAINS("1st ESCALATION", Notes@row), CONTAINS("2nd ESCALATION", Notes@row), CONTAINS("FINAL ESCALATION", Notes@row))), "", IF(OR(AND(CONTAINS("1st ESCALATION", Notes@row),[1st Date]@row = TODAY()), AND(CONTAINS("2nd ESCALATION", Notes@row), [2nd Date]@row = TODAY())), COUNTIFS(Notes:Notes, =Notes@row, [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, <= UI@row)))

    Change [1st Date]@row and [2nd Date]@row to your columns

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @acondie IS UI your auto number column?

    also you don't need the at cell for the countifs formula. UI:UI, <=UI@row. would be all that's needed if UI is your Auto number.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • acondie
    acondie ✭✭✭
    Options

    Yes UI is my autonumber row. If I adjust it as you suggest, I'm still getting the same result of the numbers not being sequential.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    just for the purpose of testing what if you remove all the formula before the count ifs to see if it performs the sequential counting. It will help narrow down the issue

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • acondie
    acondie ✭✭✭
    edited 06/20/24
    Options

    If I change the formula to this:

    COUNTIFS(Notes:Notes, "1st ESCALATION", [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, @cell <= UI@row

    I get this, which doesn't count the correct values at all. It should only count values were Notes is 1st Escalation and it's counting other values.

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/20/24
    Options

    @acondie

    The way the sequential counts work is it will still show a sequential count of everything that meets your criteria, and one that does not. Its best to simplify your formula and wrap it in a IF statement to show blank when it does not meet the requirements you are trying to count. Take for example the following formula.

    =IF(NOT(OR(CONTAINS("1st ESCALATION", Notes@row), CONTAINS("2nd ESCALATION", Notes@row), CONTAINS("FINAL ESCALATION", Notes@row))),"", COUNTIFS(Notes:Notes, =Notes@row, [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, <= UI@row))

    Anything that does not have 1st ESCALATION, 2nd ESCALATION, or Final ESCALATION will be blank. Everything else have the sequential count. that's dependent on the Escalation it is.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • acondie
    acondie ✭✭✭
    Options

    That looks like it's working properly, thank you! I also only want to count it if the status is 1st Escalation if the 1st escalation date is equal to Today and if the status is 2nd escalation if the 2nd escalation date is today. How can I modify this equation to do that?

    Thank you!

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 06/20/24 Answer ✓
    Options

    @acondie

    =IF(NOT(OR(CONTAINS("1st ESCALATION", Notes@row), CONTAINS("2nd ESCALATION", Notes@row), CONTAINS("FINAL ESCALATION", Notes@row))), "", IF(OR(AND(CONTAINS("1st ESCALATION", Notes@row),[1st Date]@row = TODAY()), AND(CONTAINS("2nd ESCALATION", Notes@row), [2nd Date]@row = TODAY())), COUNTIFS(Notes:Notes, =Notes@row, [Send Missing Information Email to Plants]:[Send Missing Information Email to Plants], 1, UI:UI, <= UI@row)))

    Change [1st Date]@row and [2nd Date]@row to your columns

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • acondie
    acondie ✭✭✭
    Options

    That works! Thank you!