Help with IF(AND) formulas
= 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!!
Comments

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.

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.

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.

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

You bet!

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


Thank you! I had no idea. Very good tip!
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.9K Get Help
 410 Global Discussions
 219 Industry Talk
 457 Announcements
 4.8K Ideas & Feature Requests
 143 Brandfolder
 136 Just for fun
 57 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 298 Events
 37 Webinars
 7.3K Forum Archives