Count a Titled Course if there is text in the cell, but not "OTHER"

Kitty

Sometimes more than 1 course is taught at the same location. I am being asked to count the number of courses taught, quarterly.  On rare occasions, a course is not taught and OTHER is entered in that column. (OTHER could mean initial meetings, graduation, etc.)

Here is the existing formula.

=IF([COURSE 2]@row = "OTHER", 0, 1)

And it works great until I get to a row where only one course was taught, so under Course 2, the cell is left blank, so now my formula is lacking a command to not count if the cell is blank.  Would this be an IF/AND formula? 

Andrée Starå

Hi Kitty,

Try something like this.

=IF(ISBLANK([COURSE 2]@row); 0; IF([COURSE 2]@row = "OTHER"; 0; 1))

The same version but with the below changes for your and others convenience.

=IF(ISBLANK([COURSE 2]@row), 0, IF([COURSE 2]@row = "OTHER", 0, 1))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

 

Paul Newcome

Try incorporating the ISBLANK function using the OR function...

 

=IF(OR(ISBLANK([COURSE 2]@row), [COURSE 2]@row = "OTHER"), 0, 1)

Kitty

Paul,

Thank you for your suggestion.  I keep getting the notice that it is incorrect.  Here is the formula I copied from my Smartsheet.  (I will have to adjust the formula for Course 1, Course 2, Course 3, and Course 4 - 4 different columns.)

=IF(OR(ISBLANK([COURSE 1]@row), [COURSE 1]@row = "OTHER"', 0, 1)

Am I missing something?

Kitty

Kitty

Thank you, Andree,

Here is the formula I've entered, and Smartsheet didn't like it.

=IF(ISBLANK([COURSE 1]@row), 0, IF([COURSE 1]@row = "OTHER"', 0, 1))

Am I missing something?

Kitty

Paul Newcome

You have an apostrophe after OTHER.

 

You have 

 

"OTHER"'

It should be

"OTHER"

Paul Newcome

Please see my comment above regarding the extra apostrophe after "Other".

Kitty

Thank you for your help.  I used a different formula and AFTER I put all commas and such in the right places, it worked.

Have a blessed day!  

Kitty

Kitty

Paul,

Thank you!!!

Paul Newcome

Happy to help! 

Andrée Starå

Glad you got it working!