Countifs & Contains formula help

Tracey Tume

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

Genevieve P.

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

Genevieve P.

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

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.

Hi @Tracey Tume

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