Issues with CONTAINS using Cross Sheet Reference

Options

Hello,


I've created a COUNTIFS formula referencing another sheet. Each section of the COUNTIFS references the same sheet so that isn't the issue I believe. Everything works up until the CONTAINS portion. Is there an error with my syntax?


=COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", CONTAINS("Onboarding", {Summary}))

Tags:

Answers

  • Genevieve P.
    Options

    Hi @RingJake

    You'll need to list the range first {Summary}, then identify the criteria you're looking for.

    Try this structure instead:

    =COUNTIFS({Status}, New1, {GSP Category}, "New GSP Onboarding", {Summary}, CONTAINS("Onboarding", @cell))


    Let me know if this works for you!

    Cheers,

    Genevieve

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

  • RingJake
    RingJake ✭✭✭✭✭
    edited 10/29/21
    Options

    Hey @Genevieve P., worked perfectly. Thank you! Did Summary have to come first because COUNTIFS is referencing it as the range for CONTAINS, then it needed to be referenced again for CONTAINS?

  • Genevieve P.
    Options

    Hi @RingJake

    Yes, exactly!

    The COUNTIFS needs this structure:

    {Range}, "Criteria", {Range}, "Criteria"...

    So we needed to list the {Summary} as a range first, before identifying what you're looking for within that column.

    Now within the "Criteria", you want to see if it contains something... therefore the CONTAINS is specific to the criteria part of the COUNTIFS structure.

    It gets tricky because the CONTAINS function also needs a Range listed. In this case though, the Range is each individual cell within the previously stated column, so that's why we use @cell.

    Cheers!

    Genevieve

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!