# Any idea why when one of my criteria is from Letter 'A' through 'M' I'm getting 'INVALID DATA TYPE'

Options
✭✭✭✭✭

but not when the criteria is N?

This is working OK,

=COUNTIFS({BIN}, "N", {Status}, "Qualified Occupied", {Move Out}, MONTH(@cell) = MONTH(Date@row), {Move Out}, YEAR(@cell) = YEAR(Date@row))

But when I replace N with A i get an error

=COUNTIFS({BIN}, "A", {Status}, "Qualified Occupied", {Move Out}, MONTH(@cell) = MONTH(Date@row), {Move Out}, YEAR(@cell) = YEAR(Date@row))

Column BIN has letters A - U.

...

• ✭✭✭✭✭✭
Options

I am guessing that you most likely don't have date values for rows where the {BIN} is A - M. Try this and see if it works:

=COUNTIFS({BIN}, [BIN #]@row, {Status}, "Qualified Occupied", {Move Out}, AND(IFERROR(MONTH(@cell), 0) = MONTH(Date@row), IFERROR(YEAR(@cell), 0) = YEAR(Date@row)))

• ✭✭✭✭✭✭
Options

I am guessing that you most likely don't have date values for rows where the {BIN} is A - M. Try this and see if it works:

=COUNTIFS({BIN}, [BIN #]@row, {Status}, "Qualified Occupied", {Move Out}, AND(IFERROR(MONTH(@cell), 0) = MONTH(Date@row), IFERROR(YEAR(@cell), 0) = YEAR(Date@row)))

• ✭✭✭✭✭
Options

I actually do have. Please see screenshot below.

I need to count the number of Move outs per BIN per month/year.

...

• ✭✭✭✭✭✭
Options

How are those dates being populated?

Did you try the amended formula?

• ✭✭✭✭✭
Options

Manually entered. I already checked, and the column are in the Date Data Type.

...

• ✭✭✭✭✭✭
Options

Ok. Did you try the amended formula yet?

• ✭✭✭✭✭
Options

Yes, seems to be working. Thank you! Any idea why my previous formula works on BIN N but not on BIN A?

...

• ✭✭✭✭✭✭
Options

I would be willing to bet that "N" does not have any rows that are "Qualified Occupied" but do not have a date. In your screenshot, you can see that "A" clearly does.

These blanks will throw an error on the YEAR and MONTH functions which pushes through to the entire formula. Thus the reason for the IFERROR on each of those functions. The IFERRORs allow the COUNTIFS to move right on through those blank date cells and we use zero so that it doesn't accidentally match up with a valid month or year.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!