Contact formula in a metric sheet
I have a sheet where the Assigned To can be multiple contacts. I can find the numbers for each. But it has been requested that I total each contact separately and then a row for Both. Below is the formula I am using. Any ideas how I can do it?
=COUNTIF({Global Communication Editorial R}, HAS(@cell, Label@row))
Katie only
Melissa only
Both Katie and Melissa
Other
None
Answers
-
This may not be the "Correct" answer, but I've struggled to evaluate multi contact columns with formulas.
What I've done to circumvent it is create "helper" columns, that evaluate the different possibilities, and then use the helper columns to do reporting.
So you would have helper checkbox columns like this,
- Column Name - "Katie Only" | =IF([Assigned TO], "Katie", 1,0)
- Column Name - "Katie" | =IF([Assigned TO], "Melissa", 1,0)
- Column Name - "Both Katie and Melissa" | =IF([Assigned TO], CONTAINS(AND("Katie","Melissa)), 1,0)
- Column Name - "None" | =IF([Assigned TO], ISBLANK(@cell),1,0)
- Your other column would depend what kind of values you could have there (you could just take the above and put them in a bunch of nested IF() statements, and have a dropdown with the different values to put it into one column - just know it will be a super long formula. =IF([Assigned TO], "Katie", "Katie Only", IF([Assigned TO], ="Melissa", "Melissa Only", IF([Assigned TO], CONTAINS(AND("Katie","Melissa)), "both", IF([Assigned TO], ISBLANK(@cell), "none", "Other")))
Then your counting formula is a simple Countifs for each column,
- =COUNTIFS([Katie], 1) .... repeat for each column
Or if your helper column is a single column, you'd use the following, repeated for each value,
- =COUNTIFS([Helper Column], "Katie Only")
-
Thanks for your easy solution. I was hoping to do it without a helper column, There may be more than two name, so hate to add to many columns to my table.
-
Here's one way to do it:
In this example I am wanting to count the number of times things appear in the Contacts Selections (multiselect) column.
In the green section above, I have the things I want to count
- Ramzi and Steve together
- Ramzi alone
- Steve alone
In the count column for both, I use this formula:
=COUNT(COLLECT([Contact Selections]:[Contact Selections], [Contact Selections]:[Contact Selections], HAS(@cell, Contact@row)))
for the other cells in the Count column, I use the same formula but subtract the value in the first cell:
=COUNT(COLLECT([Contact Selections]:[Contact Selections], [Contact Selections]:[Contact Selections], HAS(@cell, Contact@row))) - Count1
So in this example, Ramzi and Steve appear together twice and then Steve appears once by himself and Ramzi appears once by himself.
I hope this helps.
Cheers,
Ramzi
Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)
Feel free to email me: ramzi@cedartreeconsulting.com
💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!