Help with COUNTIFS function

I am trying to create a countifs statement that would count the number of intakes where SP ID # are not blank and also where Stage <> Done or Cancelled. I have tried all of the following and it continues to give me #UNPARSEABLE

=COUNTIFS(NOT(ISBLANK(SP Issue ID)))AND(Stage:Stage, <> "Done", Stage:Stage, <> "Cancelled")

=COUNTIFS(NOT(ISBLANK(SP Issue ID)))AND(Stage:Stage, "<> Done", Stage:Stage, "<> Cancelled")

=COUNTIFS(SP Issue ID, "<>", Stage:Stage,"<> Done", Stage:Stage,"<> Cancelled")

=COUNTIFS(Stage:Stage, NOT(ISBLANK(SP Issue ID)), Stage:Stage, "<> Done", Stage:Stage, "<> Cancelled")

Some help would be great!

Find more posts tagged with

Accepted answers

All comments

Dan W

=COUNTIFS(Stage:Stage, <>"", Stage:Stage, <>"Done", Stage:Stage, <>"Cancelled")

Hannahsamd

I need the SP Issue ID column to not be blank as well

Genevieve P.

Hi @Hannahsamd

In @Dan W's formula you can see that <> means "does not equal" and "" means "blank". Therefore, to add your other criteria all we have to do is check if the column does <> ""

Try:

=COUNTIFS(Stage:Stage, <>"", Stage:Stage, <>"Done", Stage:Stage, <>"Cancelled", [SP Issue ID]:[SP Issue ID], <> "")

Cheers,

Genevieve

Dan W

@Hannahsamd

FYI

the formula I posted had Stage as not blank as well. If that is not what you need then remove the first

Stage:Stage, <>"", with the inclusion of Genevieve's addition it would read:

=COUNTIFS(Stage:Stage, <>"Done", Stage:Stage, <>"Cancelled", [SP Issue ID]:[SP Issue ID], <> "")

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.