COUNTIF/AND/NOT Formula

Good afternoon;

So, I'm trying to create a Summary field for a sheet that counts the number of active projects therein. Some projects are completed (we're not archiving these yet), or on hold, or cancelled or whatever, so I don't want those in the total.

My thought was to COUNTIF everything in the [Current Status] column, but only if the values aren't equal to conditions indicating inactivity. The list of active values far outnumbers the list of inactive values, so this seemed like the best way to go. I've tried two formulas, and a few variations on each:

=COUNTIF([Current Status]:[Current Status], NOT(AND(@row = "Inactive", @row = "Cancelled", @row = "Completed/Closed", @row = "Suspended")))

=COUNTIF([Current Status]:[Current Status], AND(@row <> "Inactive", @row <> "Cancelled", @row <> "Completed/Closed", @row <> "Suspended"))

So far none of these or their variations have worked, giving me the dreaded #UNPARSEABLE error. I know I could probably do a longer formula (or several smaller ones) to subtract the count of each separate value from the total for the column, but I can't resist the urge to try nesting these functions so I can learn about them. Does anyone out there know what I'm doing wrong?

Thank you...

Find more posts tagged with

Formulas

Accepted answers

StevieSango

Step One: read the instructions...

I've used @row so frequently that I didn't realize @cell was necessary for the COUNTIF function to work properly. Once I got that straight, I built this out step-by-step and stumbled my way into the solution I needed.

I've got a couple more hurdles to clear before this is ready for Production, but the function I needed is this:

=COUNTIF([Current Status]:[Current Status], AND(@cell <> "Inactive", @cell <> "Cancelled", @cell <> "Completed/Closed", @cell <> "Suspended"))

I couldn't get NOT to work. While I got an actual number out of the formula when I used it, the number I got was actually more than the total number of projects. What I have above will work just fine.

Hope this helps someone else.

Edit: typo

All comments

StevieSango

Step One: read the instructions...

I've got a couple more hurdles to clear before this is ready for Production, but the function I needed is this:

=COUNTIF([Current Status]:[Current Status], AND(@cell <> "Inactive", @cell <> "Cancelled", @cell <> "Completed/Closed", @cell <> "Suspended"))

Hope this helps someone else.

Edit: typo

Tiffany Hanks

You could have also gotten something similar using a countifs function. This is assuming that the @cell has a single value that indicates active.

=COUNTIFS([Current Status]:[Current Status], "Active")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.