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

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

Find more posts tagged with

Non-profit

Comments

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

Course numerator.JPG

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

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

Paul,

Thank you!!!

Paul Newcome

Happy to help!

Andrée Starå

Glad you got it working!

Sign in to join the conversation!

Quick Links

Trending discussions

Dashboard Adding Widgets Spontaneously
Howdy, I am working on developing a several dashboards for our team, and for whatever reason the widgets I place are multiplying. I have copied/pasted some, but other I have just added I am ending up with dashboards that look like this: When originally I only had the top four items. It has happened on several different…
Project Management with tracking costs
I am a construction project manager and built sheets that include actual costs for individual tasks, departments and overall project. I update the costs cells as invoices come in by manually calculating then entering in new amount. Ex: Plumbing: week 1 $300, week 2 $250, week 3 $150 = $700. I enter $700 in cell. I'm…
Showing "stacked" data / Statuses of items by swimlane
I have multiple items that each have their own statuses and that roll up to "workstreams" or swimlanes. On the dashboard, I would like to show the progress of the items by swimlane without breaking it out into separate widgets for each. This would ideally be a stacked bar where each bar represented the Items and was broken…