Help with formula to count multiple select in one cell
HI! I am hoping to leverage the collective brain power of other smart than myself. I am trying to create a formula via my cross sheet that will count the number of times a contact appears in a cell. What would be even more awesome, would be to count the number of times that an option is within a cell AND the status of that project.
The issue I am running into, is when there is more than one contact in a single cell, it counts nothing. I have tried both the COUNTIF and COUNTIFS but I think this is just above my paygrade to figure out
When more then one division is listed, the COUNT formula does not work.
Formula being used. Although Central Valley and East Bay are entered at least once, they are not counting in the formula.
Answers
-
Hello @scanlan99
If it is a multi select column, you need to use HAS in the formula. Give this a try:
=COUNTIFS([Column Name]:[Column Name],HAS(@cell, "Criteria"))
For this: What would be even more awesome, would be to count the number of times that an option is within a cell AND the status of that project.
Give this a try:
=COUNTIFS([Column Name]:[Column Name],HAS(@cell, "Criteria"), [2nd Column]:[2nd Column],"Criteria")
Hope this helps!
Michelle Cullen
-
Thank you so much for the help! Will the above options still work if the formula is being used on a cross sheet?
-
Hello @scanlan99,
The formula(s) would need some minor adjustments to work on a cross sheet. Here's a super slick 2-minute video that walks you through the process.
help.smartsheet.com/sites/default/files/2022-01/smartsheet_HowToUseCrossSheetFormulas_FINAL.mp4
☺️
Michelle Cullen
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!