Help with IF(AND) formulas

Kitty

= IF[Instructor 1 Last Name]@row = "" - AND [Instructor Status]@row = "U.S. Instructor" - I need it to count as 1.  Otherwise, 0.

What I've written above is what I want to do.  Below is the formula, which gives me the dreaded #UNPARSEABLE.

=IF(AND([INSTRUCTOR 1 LAST NAME]@row=””, [INSTRUCTOR 1 STATUS]@row=”U.S. Instructor”, 1, 0))

After I can get this straightened out, then it becomes more complicated.

Then I need to check that Instructor 2's LAST NAME does not equal Instructor 1's LAST Name.

Then I need to check that Instructor 3's LAST NAME does not equal Instructor 1 & 2's LAST NAMES

Finally, I'll need to check that Instructor 4's LAST NAME does not equal Instructor 1, 2, & 3's LAST NAMES.

Sometimes we have a group of instructors who go to teach in one location. Sometimes we only have 1 instructor who will teach at least 2 courses. (Even though there are 2 courses taught, we only count it as 1 instructor even though his name will appear twice for this trip.)

I need a count of instructors who go to teach each quarter, and I need to know if they are Nationals or if they are U.S. Instructors.

All names columns are TEXT only. (My brain is slowly frying....)  Thanks for any help!!

 

Paul Newcome

The initial formula is simply an issue of a misplaced closing parenthesis. You need to move one from the end of the formula to close out the AND statement.

 

=IF(AND([INSTRUCTOR 1 LAST NAME]@row=””, [INSTRUCTOR 1 STATUS]@row=”U.S. Instructor”), 1, 0)

.

That should get you started. Next comment will approach the additional name criteria.

 

Paul Newcome

Ok. So the absolute easiest way to figure out how many unique names you have in the last name columns is to duplicate the last name columns (or move the columns themselves) so that they are next to each other. Then you can use a very basic

 

=COUNT(DISTINCT([First Last Name Column]@row:[Last Last Name Column]@row))

.

This will tell you how many unique names are within that range. So if you have 4 instructors, it will return 4. If you have 4 courses but one instructor taught two of them, then it will return 3 since you only have 3 unique names within the range.

Mike Wilday

I am also noticing that a lot of the " marks are slanted which means you probably constructed your formula in a word processor like Microsoft Word or Google Docs. Word processors tend to substitute straight quotes with slanted quotes, and those quotes will cause your formula to be unparseable as well. 

Paul Newcome

I noticed that and just forgot to mention it. Thanks, Mike!

Mike Wilday

 You bet! 

Kitty

Thank you SO VERY MUCH!  Everything you posted worked!  So very grateful.  Now I can surface "numbers" to the dashboard.  Blessings!

Paul Newcome

Happy to help! 

Kitty

Thank you!  I had no idea. Very good tip!