Getting #invalid data type error for this formula

keesuri25

=COUNTIFS([Engagement Phase]:[Engagement Phase], "COMPLETED", YTD:YTD, YEAR(@cell) = 2021)

Mike TV

@keesuri25

Your formula is working for me:

What type of column is YTD on your sheet? It needs to be a Date type column.

Paul Newcome

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)

Paul Newcome

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)

Mike TV

@keesuri25

Your formula is working for me:

What type of column is YTD on your sheet? It needs to be a Date type column.

keesuri25

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

keesuri25

Paul Newcome

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)

keesuri25

Now back to Invalid data type..

keesuri25

Paul Newcome

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)

keesuri25

@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.

Paul Newcome

Happy to help. 👍️