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!