COUNTIFS Formula with NOT function

Cameron G

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.

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")

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.