How to Count Instances with Criteria

I have a running report(example report link below) that lists accounts that are/will be overdrawn. Therefore, need cash deposited to the account. If the Execute column is checked then it should be included in the count. If the Do Not Execute column is checked or neither column is checked then it need to refer to the date. Basically, I need to count each instance while excluding the duplicates. I have manually input a count column.

Here are my examples.

Example 1: I want to count the first instance for Taylor that occurred 1/5/23. And because nothing was executed, the account continues to appear on the following business days reports. I do not want these counted since they are duplicates. (It doesn't matter which instance is counted; I just need these 3 to count as 1)

Example 2: No action was taken the first time this account appeared on the report. Action was take the second time it appeared. I want to count only the instance where action was taken.

Example 3: This account appears twice. Once at the beginning of the month and once towards the middle of the month. I want to count both of these instances since they are not duplicates.

Example 4: These accounts only appeared once. I want them counted regardless of the action.



I'm stumped on how to accomplish this one. Any help is appreciated!

Brittany

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭

    Hi @Brittany S

    Your scenarios are too varied to be able to count with a formula.

    For instance, in Ex 3 Karen is listed twice with 2 different dates and you want to count both. But in Ex 2, Joe is listed the same way, but you only want it counted once.

    A formula can't figure that out for you.

    You may want to try and explain here what your Executed and other columns mean and how that relates to how you actually count. Then maybe we can derive a formula for you.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!