Formula working in some cases in my sheet, not in others

Question

Hello!

I'm trying to write a formula to count and compare how many new clients an individual staff member managed to get over the line in a given month.

I'm experiencing an odd issue where in certain places in my sheet, the formula is working, but in other places it's not.

So for example, this one is returning a number no problem:

=COUNTIFS({Staff Member}, "John Smith", {Conversion}, "No", {Date}, (MONTH(@cell) = 9), {Date}, (YEAR(@cell) = 2020))

But if I copy that formula into another column and switch "No" for "Yes" like so:

=COUNTIFS({Staff Member}, "John Smith", {Conversion}, "Yes", {Date}, (MONTH(@cell) = 9), {Date}, (YEAR(@cell) = 2020))

I get the #INVALID DATA TYPE error.

{Staff Member} is a Contact List column, {Conversion} is a symbol column (red, yellow and green traffic lights), {Date} is a date column, if that helps.

Is there a more stable way to write this formula?

Thank you!

Iris Riddell · Accepted Answer

I figured it out! Will answer my own question here since I figure it might be useful for others having a similar issue. I worked in an IFERROR function which seems to have fixed it:

=COUNTIFS({Staff Member}, "John Smith", {Conversion}, "Yes", {Date}, OR(IFERROR(MONTH(@cell), 0) = 9), {Date}, OR(IFERROR(YEAR(@cell), 0) = 2021))

Still not sure why my original formula was working intermittently rather than not at all, but this one seems a lot happier. 😊