COUNT IFS of status with multiple drop down selections

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.



Best Answers

  • Ric T
    Ric T ✭✭✭✭✭
    Answer ✓

    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
    Ric T ✭✭✭✭✭
    edited 01/31/24 Answer ✓

    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

Answers

  • Ric T
    Ric T ✭✭✭✭✭
    Answer ✓

    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

  • Much appreciated @Ric T - that worked!

  • @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
    Ric T ✭✭✭✭✭
    edited 01/31/24 Answer ✓

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!