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
-
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
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!