count an instructor only if last name is different
We send instructors to teach globally. I need to keep count of instructors on the field, quarterly. Sometimes 1 instructor goes to teach 2 courses. So the count would be 1 instructor, 2 courses.
Sometimes 2 instructors go to teach 2 courses. So that count would be 2 instructors, 2 courses.
This formula seems to work where only 2 instructors are involved. Sometimes I send a group of 3 or 4 instructors. That's where I'm having difficulty with my formula. Or maybe someone could advise a simpler formula?
I set up a numerator column beside each section for Instructor/Course taught.
Formula for Instructor 1 is: =IF([INSTRUCTOR 1 LAST NAME]@row = "", 0,1)
Formula for Instructor 2 is: =IF([INSTRUCTOR 2 LAST NAME]@row = [INSTRUCTOR 1 LAST NAME]@row = 0,1)
Formula for Instructor 3 - I need help figuring out how to say if Instructor 3's last name does not equal Instructor 1's Last Name or Instructor 2's Last Name, then count as 1. (And then the same for Instructor 4.)
Any ideas?
THANK YOU!
Kitty Sandel
Comments
-
What type of columns are you using for the instructors' names? If they are text type or a contact type WITHOUT the option turned on to have multiple contacts per cell, then you can use a CONTAINS function to search a range of cells for a specific data string. Something like this...
=IF(CONTAINS([INSTRUCTOR 3 LAST NAME]@row, [INSTRUCTOR 1 LAST NAME]@row:[INSTRUCTOR 2 LAST NAME]@row), 0, 1)
.
For instructor 4 it would look like this...
=IF(CONTAINS([INSTRUCTOR 4 LAST NAME]@row, [INSTRUCTOR 1 LAST NAME]@row:[INSTRUCTOR 3 LAST NAME]@row), 0, 1)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!