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

Kitty
Kitty ✭✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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? 

Formula to count courses with exception of OTHER.JPG

Tags:

Comments

  • Andrée Starå
    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

    work-bold

     

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

     

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

    thinkspi.com

  • Kitty
    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

    Course numerator.JPG

  • 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

    Course numerator_0.JPG

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You have an apostrophe after OTHER.

     

    You have 

     

    "OTHER"'

    It should be

    "OTHER"

    thinkspi.com

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    thinkspi.com

  • Kitty
    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
    Kitty ✭✭✭✭✭✭
    edited 12/04/19

    Paul,

    Thank you!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

    thinkspi.com

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Glad you got it working!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.