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
-
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
-
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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.9K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!