Getting #invalid data type error for this formula
=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", YTD:YTD, YEAR(@cell) = 2021)
Best Answers
-
Your formula is working for me:
What type of column is YTD on your sheet? It needs to be a Date type column.
-
It looks like you have a minor syntax issue. Whenever you reference a column name that has a space, number, and/or special character in it, you have to wrap the column name in square brackets whereas you wrapped the entire reference in square brackets. Try this:
=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date]:[PoC End date], YEAR(@cell) = 2022)
-
Double check that the[PoC End date] is in fact set as a date type column. If it is then I believe the issue is because you most likely have some blanks in the column which throws an error on the YEAR function (no date to pull a year from). In that case you will need to modify your YEAR criteria and add in an IFERROR.
=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date]:[PoC End date], IFERROR(YEAR(@cell), 0) = 2022)
Answers
-
Your formula is working for me:
What type of column is YTD on your sheet? It needs to be a Date type column.
-
It was not a date column. I changed it to
=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date:PoC End date], YEAR(@cell) = 2022)
Now its UNPARSEABLE
-
It looks like you have a minor syntax issue. Whenever you reference a column name that has a space, number, and/or special character in it, you have to wrap the column name in square brackets whereas you wrapped the entire reference in square brackets. Try this:
=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date]:[PoC End date], YEAR(@cell) = 2022)
-
Now back to Invalid data type..
-
Double check that the[PoC End date] is in fact set as a date type column. If it is then I believe the issue is because you most likely have some blanks in the column which throws an error on the YEAR function (no date to pull a year from). In that case you will need to modify your YEAR criteria and add in an IFERROR.
=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", [PoC End date]:[PoC End date], IFERROR(YEAR(@cell), 0) = 2022)
-
@Paul Newcome - Thank you so much. Finally worked. It was the blanks in the column that was causing the error. Really appreciate your prompt replies.
Help Article Resources
Categories
Check out the Formula Handbook template!