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

Iris Riddell

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

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. 😊

Iris Riddell

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. 😊

Genevieve P.

Hi @Iris Riddell

I'm glad you figured it out! Yes, the IFERROR statement can really help around MONTH functions. This is because a blank cell or a cell with text will be seen as "invalid". It sounds like your "Yes" criteria was associated with some rows that potentially had a blank date.

Thanks for posting your solution!

Genevieve