count an instructor only if last name is different

Kitty · 08/12/19

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

Paul Newcome · 08/12/19

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)

count an instructor only if last name is different

Comments

Help Article Resources

Categories