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

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.
...
Best Answer
-
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)))
Answers
-
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)))
-
I actually do have. Please see screenshot below.
I need to count the number of Move outs per BIN per month/year.
...
-
How are those dates being populated?
Did you try the amended formula?
-
Manually entered. I already checked, and the column are in the Date Data Type.
...
-
Ok. Did you try the amended formula yet?
-
Yes, seems to be working. Thank you! Any idea why my previous formula works on BIN N but not on BIN A?
...
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 500 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!