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

acondie

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!

Mark.poole

@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

Mark.poole

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

acondie

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

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

acondie

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

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

acondie

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

@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

acondie

That works! Thank you!