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

heyjay · 10/19/22

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.

Paul Newcome · 10/19/22

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

Paul Newcome · 10/19/22

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

heyjay · 10/19/22

I actually do have. Please see screenshot below.

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

Paul Newcome · 10/19/22

How are those dates being populated?

Did you try the amended formula?

heyjay · 10/19/22

https://community.smartsheet.com/discussion/comment/347847#Comment_347847

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

Paul Newcome · 10/19/22

Ok. Did you try the amended formula yet?

heyjay · 10/19/22

https://community.smartsheet.com/discussion/comment/347855#Comment_347855

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

Paul Newcome · 10/20/22

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.

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

Best Answer

Answers

Help Article Resources

Categories