#INVALID DATA TYPE Error

I am receiving an #INVALID DATA TYPE Error when I have blanks in the dates I am referencing. The formula is summing the equipment pieces based on the delivery month, but the delivery date can be added later in the project, so there will often be blanks. My formula is below:

Adding an IFERROR avoids the error message, but I would like for the formula to work for the data that is present in the other months. It is feeding a chart that is a lot less meaningful without the not accepted showing up.

Any ideas on this? Appreciate your help in advance!

Find more posts tagged with

Formulas

Reports

functionality

Accepted answers

David Joyeuse

Hi @Angela Cisneros

Just add an ISDATE function to your SUMIFS so it only takes Dates into account.

Your formula would be like this then:

=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, AND(ISDATE(@cell),MONTH(@cell)=Value72))

Also, you can use the IFERROR on the MONTH function:

=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, IFERROR(MONTH(@cell), 0)=Value72)

Hope it helped!

All comments

David Joyeuse

Hi @Angela Cisneros

Just add an ISDATE function to your SUMIFS so it only takes Dates into account.

Your formula would be like this then:

=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, AND(ISDATE(@cell),MONTH(@cell)=Value72))

Also, you can use the IFERROR on the MONTH function:

=SUMIFS({Summary Roll-Up - Equipment}, {Summary Roll-Up - Latest Delivery}, IFERROR(MONTH(@cell), 0)=Value72)

Hope it helped!

Angela Cisneros

Thanks for those suggestions. The ISDATE did not work, but the IFERROR on the month did.

Thanks!

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.