#INVALID DATA TYPE when counting column without a value

Hi guys, im trying to use CountIFs to count all projects that have a completion date by month. Not all projects are complete so there that are those that do not have an end date has the error #invalid data type".

I tried using this formula, but didn't work. Could I have assistance fixing the formula? If there is an invalid data type, it just wouldn't count that line or return 0 as a value.

=COUNTIFS([Completion Month]:[Completion Month], "January", IFERROR([Completion Month]1, 0))

but I need to do a countifs for all months January-December.

Thanks in advance!

Invalid Data Type.PNG

Find more posts tagged with

Formulas

Accepted answers

SteyJ

Use IFERROR function at beginning of the formula:

=IFERROR(COUNTIFS([Completion Month]:[Completion Month], "January", [Completion Month]1), "")

All comments

SteyJ

Use IFERROR function at beginning of the formula:

=IFERROR(COUNTIFS([Completion Month]:[Completion Month], "January", [Completion Month]1), "")

AndrewT

Hey Jacob! Thanks for the insight. I tried that formula as well! Though it didn't give an invalid data type error, it did return a blank "" for all months no matter the value I put in. Could there be something else im missing? We're on the right track for sure!

SteyJ

https://community.smartsheet.com/discussion/comment/420198#Comment_420198

If all you need to do is count how many times the month appears use this formula:

=IFERROR(COUNTIFS([Completion Month]:[Completion Month], "January"), "")

AndrewT

Hi Jacob, I think im inputting it correctly, but still coming with a blank answer for any month I input.

It still shows as blank no matter the month

SteyJ

https://community.smartsheet.com/discussion/comment/420355#Comment_420355

This is likely because the system is confused about the INVALID DATA TYPE in your completion month column. I would try to resolve those errors. Another issue is that the formatting of the text may be throwing off the formula. I've seen COUNTIF not pickup the formula because of weird text formatting.

Try this to check:

=COUNTIF([Completion Month]2, "December")

You should get a value of 1, then try with the below formula, you should still get 1

=COUNTIF([Completion Month]3, "October")

If you get 1 for both, you know the issue is with the #INVALID DATA TYPE while trying to use the count function.

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.