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}))
Answers
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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?
-
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
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.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!