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!