Use of countifs when multiple dropdown values are in cells

Ed Olszanowski

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?

Genevieve P.

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

Genevieve P.

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

Ed Olszanowski

Genevieve P Rocks!!! Thank you so much! Spent a few hours trying to get that figured out. It worked!

Genevieve P.

@Ed Olszanowski So glad to hear it worked for you! 🙂