#INVALID DATA TYPE when counting column without a value

AndrewT · 02/15/24

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

SteyJ · 02/15/24

Use IFERROR function at beginning of the formula:

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

SteyJ · 02/15/24

Use IFERROR function at beginning of the formula:

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

AndrewT · 02/15/24

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 · 02/15/24

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 · 02/16/24

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 · 02/16/24

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.

#INVALID DATA TYPE when counting column without a value

Best Answer

Sincerely,

Answers

Sincerely,

Sincerely,

Sincerely,

Help Article Resources

Categories