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.

Find more posts tagged with

Accepted answers

Paul Newcome

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

All comments

Paul Newcome

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

I actually do have. Please see screenshot below.

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

Paul Newcome

How are those dates being populated?

Did you try the amended formula?

heyjay

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

Ok. Did you try the amended formula yet?

heyjay

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.