Countifs & Contains formula help

Options

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 Admin
    Answer ✓
    Options

    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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    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

  • Tracey Tume
    Tracey Tume ✭✭✭✭✭
    Options

    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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Tracey Tume

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!