I am having troubles integrating a contains formula into my Countifs formula.

I am wanting to count anytime a certain user has a certain type of follow up needed comment. The excel sheet comes from a workspace where users can select multiple options so I am looking to grab comments that contain a certain phrase. Below are my current formula, a screen shot of the metric sheet, and a filtered down version of the source sheet with the two main range columns highlighted.

=COUNTIFS({Retro Audit Report 2 Cred Required Comment}, (CONTAINS("Education", {Retro Audit Report 2 Cred Required Comment})), {Retro Audit Report 2 Default User}, User@row)


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Spencor_Luze

    In your CONTAINS function, instead of re-typing the entire {reference} again, you'll want to use @cell to let the function know you want to search each cell in the range for your word.

    See: Create Efficient Formulas with @cell and @row

    Try this formula:

    =COUNTIFS({Retro Audit Report 2 Cred Required Comment}, CONTAINS("Education", @cell), {Retro Audit Report 2 Default User}, User@row)


    Note that I also removed the extra (parentheses) around the CONTAINS Function, you can list it right away. Let me know if this works for you and makes sense!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!