Multi Select HAS Formula

Options

Hi

I'm trying to get a sheet to count any projects where Finance Teams and / or Finance Systems are required to deliver a project at a particular status for a Portfolio dashboard metric. I'm currently using this formula but, it's not pulling the right info back - I'm getting a zero return when I'm expecting a number.

=COUNTIFS({Project Intake l Portfolio}, [Metric Name]@row, {Project Status}, "New", {Project Intake Specialist Resource}, HAS(@cell, "Finance Teams"), {Project Intake Specialist Resource}, HAS(@cell, "Finance Systems"))

TIA

Cheryl

Tags:

Best Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓
    Options

    the way its currently set up its only pulling if it has both Finance Teams and Finance Systems? Instead try.

    =COUNTIFS({Project Intake l Portfolio}, [Metric Name]@row, {Project Status}, "New", {Project Intake Specialist Resource}, OR(HAS(@cell, "Finance Teams"), HAS(@cell, "Finance Systems")))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Cheryl C
    Cheryl C
    Answer ✓
    Options

    Brilliant. Thanks @Mark.poole thats done the trick.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    edited 05/31/24 Answer ✓
    Options

    the way its currently set up its only pulling if it has both Finance Teams and Finance Systems? Instead try.

    =COUNTIFS({Project Intake l Portfolio}, [Metric Name]@row, {Project Status}, "New", {Project Intake Specialist Resource}, OR(HAS(@cell, "Finance Teams"), HAS(@cell, "Finance Systems")))

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

  • Cheryl C
    Cheryl C
    Answer ✓
    Options

    Brilliant. Thanks @Mark.poole thats done the trick.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!