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

Options
✭✭✭✭✭
edited 12/09/19

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?

Tags:

• ✭✭✭✭✭✭
Options

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

You have an apostrophe after OTHER.

You have

"OTHER"'

It should be

"OTHER"

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭
Options

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

• ✭✭✭✭✭
edited 12/04/19
Options

Paul,

Thank you!!!

• ✭✭✭✭✭✭
Options
• ✭✭✭✭✭✭
Options