Issues with CONTAINS using Cross Sheet Reference

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.
    Genevieve P. Employee Admin

    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

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

    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.
    Genevieve P. Employee Admin

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!