Countif based on array of values

Ana Guilherme · 04/18/23

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!

Austin Smith · 04/18/23

@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 · 04/18/23

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 · 04/18/23

@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 · 04/18/23

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!