Countif based on array of values

Options
Ana Guilherme
Ana Guilherme ✭✭
edited 04/18/23 in Formulas and Functions

Hello,

I hope this is possible!🤣

I have this list of states where a "Task Name" can have multiple sub-states listed on "Column 1"

I've already "merged" the sub-states. Example below with Blocker --> "Test Blocked" & "Blocked"

=JOIN(COLLECT([Column 1]82:[Column 1]89, [Task Name]82:[Task Name]89, [Column 4]86), " ")

and now what I would like to reach is a countif on my database of values, that would be count the number of Stories that either have "Test Blocked" or "Blocked" as I'm selecting the parent "Blocked"


I case I would have "To Do" I would search for all sub-states under To Do, that would be "To Do", "new", "Pending Sign off" or "Reopened"

So far I've been working with Or(contains()), where I have a contains for each type, but every time a sub-state changes I need to re-arrange all my formulas manually, so I'm seeking for a more automated way.


Thank you in advance!

Tags:

Answers

  • Austin Smith
    Austin Smith ✭✭✭✭✭
    Options

    @Ana Guilherme

    Pretty sure you're going to want a nested if function. Try this to get a master status:

    =if(or([column1]@row = "blocked", [column1]@row = "test blocked"), "BLOCKED", if(or([column1]@row = "to do", [column1]@row = "new", [column1]@row = "pending sign off", [column1]@row = "reopened"), "TO DO", if([column1]@row = "ready, "READY", if([column1]@row = "research", "RESEARCH", "ERROR"))))

    If you're truly just looking for a count, try:

    =countif([column1]:[column1], or([column1]@row = "to do", [column1]@row = "new", [column1]@row = "pending sign off", [column1]@row = "reopened")

  • Ana Guilherme
    Options

    hello @Austin Smith thank you for your prompt reply.

    I do not want to select the sub-stages manually, I wish to depending on the master stage, be able to use the sub-stages on a CountIf formula.

    Example:

    If I want to search for "Blocked" I effectively want for the CountIf to have as criteria the Sub-Stages ("Test Blocked and "Blocked") without me having the need to list those.

    Maybe it's not possible to count an array of options without having the possibility to write them manually.

    Thank you!

  • Austin Smith
    Austin Smith ✭✭✭✭✭
    Options

    @Ana Guilherme

    Try the following formula in the highlighted cell under To Do. (where you show 2)

    =countif([column5]:[column5], OR(@cell = "to do", @cell ="new", @cell ="pending sign off", @cell ="reopened"))

    You'll just need to change the trigger words for Blocked sub-categories for the Blocked count cell. You don't need a written master list of available options for it to work.

    =countif([column5]:[column5], OR(@cell = "blocked", @cell ="test blocked")

  • Ana Guilherme
    Ana Guilherme ✭✭
    edited 04/18/23
    Options

    Hello @Austin Smith Thank you for your support.

    I have added your formula and then I have changed a sub-stage from "New" to "New Status" and instead of the To Do give me 2 counts only gives me 1.

    I would like to find a way to sum the number of times a sub-stage appears based on the defined stage, without having to write the sub-stages in the formula.

    In Excel I have reach this:

    The numbers is purple 1 and 2 are corresponding to the number of times the sub-stages of Blocked appear. 1 for Test Blocked when looking for Blocked + Story on the column E and F and 2 for the Blocked when looking for the Blocked + Story on column E and F, of course based on the master and sub-stages on column B and C.

    The final step would be to be able to sum both in the same formula! ahahah and then figure out how to do that in Smartsheet!

    Thank you so much for the support and prompt replies!

  • Ana Guilherme
    Options

    Something like this would be enough and so I can work on a workaround.


  • Ana Guilherme
    Options

    Hello @Genevieve P. ,

    Apologies for my absence ( I took a time-off in the last days)

    Yes, I was able to have a workaround for both situations after @Paul Newcome suggestion in the other post!

    Thank you, for all the support 🤗

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!