COUNTIFS Formula with NOT function

I'm trying to count the number of cells filled with a date within a column, except for rows that have status as "On Hold". The formulas I've tried are:

=COUNTIFS({Charter Date}, AND(IFERROR(MONTH(@cell, 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Approved Gate}, NOT(@cell = "On Hold")))

Output: Invalid Data Type error.

=COUNTIFS({Charter Date}, AND(IFERROR(MONTH(@cell, 0) = 1, IFERROR(YEAR(@cell), 0) = 2022), {Approved Gate}, <>"On Hold"))

Output: Invalid Data Type error.

Any thoughts? Appreciate the help.

Find more posts tagged with

Accepted answers

Sameer Karkhanis

That's probably because some of the rows may have no date (aka blank cell) in your {Charter Date} range. Additionally the arguments to the MONTH function are incorrect.

Try this:

=COUNTIFS({Charter Date}, NOT(ISBLANK(@cell)), {Charter Date}, AND(MONTH(@cell)=1, YEAR(@cell)=2022), {Approved Gate}, <>"On Hold")

All comments

Sameer Karkhanis

That's probably because some of the rows may have no date (aka blank cell) in your {Charter Date} range. Additionally the arguments to the MONTH function are incorrect.

Try this:

=COUNTIFS({Charter Date}, NOT(ISBLANK(@cell)), {Charter Date}, AND(MONTH(@cell)=1, YEAR(@cell)=2022), {Approved Gate}, <>"On Hold")

Cameron G

Thank you Sameer! This works great - just to clarify, does the Charter Date range need to be referenced twice separately or could the NOT, MONTH and YEAR functions be combined into one Charter Date range reference? Thanks again.

Sameer Karkhanis

Good thinking, and did you try that? My take is it will not work. The blank rows need to be eliminated before those functions are processed.

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.