Use of countifs when multiple dropdown values are in cells

Options

I'm attempting to count a range of cells (columns) where the value may have one or more values included in the cell from a dropdown value.

Example: Workstream may have "Architecture" "testing" "UAT" included in the cell in one instance and in others only "Architecture". My formula seems to be counting only if "Architecture" alone is in the cells. My formula also looks for % complete less than 100% and start and finish dates in the past.

=COUNTIFS(Workstream:Workstream, "Architecture", [% Complete]:[% Complete], <1, Status:Status, <>"withdrawn", Start:Start, <TODAY(), Finish:Finish, <TODAY())

How can I change the formula to work instead of looking for exact "Architecture" to more of Contains Architecture?

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓
    Options

    Hi @Ed Olszanowski

    With a multi-select column, you'll want to add in the HAS function to see if the cell has this value (along with other values).

    Try this:

    =COUNTIFS(Workstream:Workstream, HAS(@cell, "Architecture"), [% Complete]:[% Complete], <1, Status:Status, <>"withdrawn", Start:Start, <TODAY(), Finish:Finish, <TODAY())

    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!