Struggling with COUNTIFS and CONTAINS

Options

Hello! I have read through a bunch of questions about COUNTIF and CONTAINS formula but none seem to be working for me.

I have a metric sheet in which I need to count the number of projects in a particular status for a particular business unit. The business unit field on the source sheet is a multi-select field, projects can impact more than one BU.

My formula is: =COUNTIFS({Status}, Metric@row, {Intake: BU}, CONTAINS("AFS", {Intake: BU}))

If the Status field contains the value in the metric column for this row, and the Intake BU contains AFS, count the row. This is returning zero which is incorrect.

I also tried HAS instead of CONTAINS and couldn't get that to work either.

Thanks in advance!

Andrea

Andrea Zenner

Program Manager | Infrastructure & Operations

Apogee Enterprises

EAP | Mobilizer | Core Product Certified | Superstar

azenner@apog.com

Tags:

Best Answer

  • Andrea Zenner
    Andrea Zenner ✭✭✭✭✭
    Answer ✓
    Options

    I figured it out. Not sure I understand how it's looking for the value but this formula works.

    =COUNTIFS({Status}, Metric@row, {Intake: BU}, CONTAINS("AFS", @cell))


    Thanks!!

    Andrae

    Andrea Zenner

    Program Manager | Infrastructure & Operations

    Apogee Enterprises

    EAP | Mobilizer | Core Product Certified | Superstar

    azenner@apog.com

Answers

  • Andrea Zenner
    Andrea Zenner ✭✭✭✭✭
    Answer ✓
    Options

    I figured it out. Not sure I understand how it's looking for the value but this formula works.

    =COUNTIFS({Status}, Metric@row, {Intake: BU}, CONTAINS("AFS", @cell))


    Thanks!!

    Andrae

    Andrea Zenner

    Program Manager | Infrastructure & Operations

    Apogee Enterprises

    EAP | Mobilizer | Core Product Certified | Superstar

    azenner@apog.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!