COUNT IFS of status with multiple drop down selections

Liz Wallace

Hello-

I need to calculate the total # of times a status appears, and it is also dependent on other criteria being met.

The current formula I am using will only be counted if that status is in the only status in the cell.

=COUNTIFS({Weekly Status Product Development Range 1}, "OFF TRACK", {Weekly Status Product Development Range 3}, "German")

I need to count the statuses in the screenshot below but the ones that also have "Tech" as part of the status are not being counted.

Ric T

Hi @Liz Wallace,

Try using the HAS function and @cell for your multi-select dropdown column , so it'll look something like this:

=COUNTIFS({Weekly Status Product Development Range 1}, HAS(@cell, "OFF TRACK"), {Weekly Status Product Development Range 3}, "German")

Cheers,

Ric

Ric T

Hi @Liz Wallace, great questions and keep 'em coming!

You're very close - to search for "2023" in this case, you'd want to use the CONTAINS function instead, so using your second formula, it'll look like:

=COUNTIFS({Weekly Status Product Development Range 4}, CONTAINS("2023", @cell), {Weekly Status Product Development Range 5}, "1")

HAS searches for specific values, so for that to work in your screenshot, you'll need to search for "2023-Fall". CONTAINS will search for strings or characters containing the criteria, hence it'll pickup "2023" in your Launch column.

Cheers,

Ric

Ric T

Hi @Liz Wallace,

Try using the HAS function and @cell for your multi-select dropdown column , so it'll look something like this:

=COUNTIFS({Weekly Status Product Development Range 1}, HAS(@cell, "OFF TRACK"), {Weekly Status Product Development Range 3}, "German")

Cheers,

Ric

Liz Wallace

Much appreciated @Ric T - that worked!

Liz Wallace

@Ric T I have a similar question... I am trying to count the times the year appears in the black rows (Hierarch=0 for those rows)

I tried both =COUNTIFS({Weekly Status Product Development Range 4}, CONTAINS(@cell, "2023"), {Weekly Status Product Development Range 5}, "1")

and =COUNTIFS({Weekly Status Product Development Range 4}, HAS(@cell, "2023"), {Weekly Status Product Development Range 5}, "1")

But I am returning a zero for the value.

Ric T

Hi @Liz Wallace, great questions and keep 'em coming!

You're very close - to search for "2023" in this case, you'd want to use the CONTAINS function instead, so using your second formula, it'll look like:

=COUNTIFS({Weekly Status Product Development Range 4}, CONTAINS("2023", @cell), {Weekly Status Product Development Range 5}, "1")

HAS searches for specific values, so for that to work in your screenshot, you'll need to search for "2023-Fall". CONTAINS will search for strings or characters containing the criteria, hence it'll pickup "2023" in your Launch column.

Cheers,

Ric