#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!
Best Answer
-
Use IFERROR function at beginning of the formula:
=IFERROR(COUNTIFS([Completion Month]:[Completion Month], "January", [Completion Month]1), "")
Sincerely,
Jacob Stey
Answers
-
Use IFERROR function at beginning of the formula:
=IFERROR(COUNTIFS([Completion Month]:[Completion Month], "January", [Completion Month]1), "")
Sincerely,
Jacob Stey
-
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!
-
If all you need to do is count how many times the month appears use this formula:
=IFERROR(COUNTIFS([Completion Month]:[Completion Month], "January"), "")
Sincerely,
Jacob Stey
-
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
-
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.
Sincerely,
Jacob Stey
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!