Countifs & Contains formula help

Hi

I have created this formula with cross sheet references but it is not working and I do not know why!

Help appreciated please?

Each column reference is looking at a full column and all searched are a partial searches of what data is in a cell ie "Platinum Central" is only part of the name in the cell.

=COUNTIFS({Schedule Archive 2021.2 Post Covid 2}, CONTAINS("Platinum Central", @cell), {Schedule Archive 2021.2 Post Covid 3}, NOT(HAS(@cell, "Kicks"), {Schedule Archive 2021.2 Post Covid 3}, NOT(HAS(@cell, "Benchtops"))))

Have a great weekend and thanks in advance for your help

Regards Tracey

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Tracey Tume

    It looks like you just have a closing parentheses in the wrong place!

    If you take out the inside contents you can see that after your first NOT(HAS() there's a closing parentheses missing:

    =COUNTIFS({Range}, CONTAINS(), {Range}, NOT(HAS(), {Range}, NOT(HAS())))

    It should be:

    =COUNTIFS({Range}, CONTAINS(), {Range}, NOT(HAS()), {Range}, NOT(HAS()))

    With one taken off the end. Does that make sense?

    Try this:

    =COUNTIFS({Schedule Archive 2021.2 Post Covid 2}, CONTAINS("Platinum Central", @cell), {Schedule Archive 2021.2 Post Covid 3}, NOT(HAS(@cell, "Kicks")), {Schedule Archive 2021.2 Post Covid 3}, NOT(HAS(@cell, "Benchtops")))


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Tracey Tume

    It looks like you just have a closing parentheses in the wrong place!

    If you take out the inside contents you can see that after your first NOT(HAS() there's a closing parentheses missing:

    =COUNTIFS({Range}, CONTAINS(), {Range}, NOT(HAS(), {Range}, NOT(HAS())))

    It should be:

    =COUNTIFS({Range}, CONTAINS(), {Range}, NOT(HAS()), {Range}, NOT(HAS()))

    With one taken off the end. Does that make sense?

    Try this:

    =COUNTIFS({Schedule Archive 2021.2 Post Covid 2}, CONTAINS("Platinum Central", @cell), {Schedule Archive 2021.2 Post Covid 3}, NOT(HAS(@cell, "Kicks")), {Schedule Archive 2021.2 Post Covid 3}, NOT(HAS(@cell, "Benchtops")))


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭

    Thanks SO much Genevieve for noticing my mistake so it could be corrected.

    On further reading, it turns out that the HAS option is for dropdowns so was wrong to use it.

    I have used CONTAINS and I think it has worked. I will keep your information in mind for another time as it is all learning!

    Regards

    Tracey Tume

  • Hi @Tracey Tume

    Good point! Yes, HAS is intended for multi-select. I'm glad you were able to figure something out.

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!