COUNTIFS multi drop down list and check box

I have a sheet where I am counting the number of variance root causes if the milestone field is checked (first column). Variance root cause is a multi select drop down. In the above example, the count would equal 4 (I would not count the root cause for task 3 since milestone is not checked.

I have the formula working with multiple COUNTIFS added together but think there must be a cleaner/better way to do it.

=COUNTIFS({Root Cause}, CONTAINS("Build Challenges", @cell), {Milestone}, 1) + second equation, etc…

Any thoughts or hints would be appreciated.

Thank you!

Answers

  • Ward.Hively
    Ward.Hively ✭✭✭✭

    Hello SteveE,

    I like those snippets. Pretty neat!

    This is what I would try aside from demarcating each individual drop-down possibility, a look-up table would be able to do this same thing. Personally, I prefer the formula because it is cleaner and if isnt broke… why fix it?

    Hope this helps!

    Respectfully,

    Ward Hively

    President and Chief Consultant

    🌉Skyway Consulting Co.🌉

    Need Smartsheet Help? Schedule a complementary consultation click SCHEDULE

    Does your Dashboard need a map that updates from Smartsheet Data? We pioneered 101+ ways to add a map to a Smartsheet Dashboard.

    Smartsheet and GIS Integrations

    ArcGIS Online

    ✅ Arc Py

    ✅ Smartsheet Advanced

    ✅ Manage your GIS records from Dynamic View, Sheet or Report

    ✅ No Code (Limited Applications Apply)

    Website: https://skywayconsultingco.com

    LinkedIn: (49) Ward Hively | LinkedIn

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!