count an instructor only if last name is different

Kitty
Kitty ✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!